StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are a couple of things that are not in your code. Seeing that you're approaching this process using an iteration/loop, some confusion is only expected. My main concerns though are that you use <code>ActiveSheet</code> and <code>Select</code> and that your final actions refer to only one cell (<code>Range("A2"</code>). This means the resultant action of each iteration changes just this cell. See below for one possible approach to this.</p> <p>First, you need to iterate over the names of the worksheets found in <code>Master Sheet, Column AA</code>. Next, you have to check for the last row of the worksheet that corresponds to this name. Based on your code, the range of cells from <code>A2</code> to the last row of <code>Column N</code> is copied to the Recompile sheet, starting with Cell <code>A2</code> (I'm assuming you have headers).</p> <p>However, after every copy, you need to do three things as well: 1. Move to the next name in your list of sheet names. 2. Find the new last row of the sheet corresponding to the next name. 3. Find the new last row of the Recompile sheet, so you can start there.</p> <p>Now, you need this to loop as many times as there are sheets. The logic of this gives rise to the following code (<strong>untested</strong>, please do in a backup copy of your workbook):</p> <pre><code>Private Sub Boom() Dim ShMaster As Worksheet, ShRecompile As Worksheet Dim ShName As String, RngToCopy As Range Dim LRowSrc As Long, LRowRecompile As Long 'Src is other sheets Dim LRowMaster As Long 'This will be our iteration limit. Dim Iter As Long With ThisWorkbook Set ShMaster = .Sheets("Master Sheet") Set ShRecompile = .Sheets("Recompile") End With LRowMaster = ShMaster.Cells(Rows.Count, 27).End(xlUp).Row For Iter = 2 to LRowMaster ShName = ShMaster.Cells(Iter, 27).Value With ThisWorkbook.Sheets(ShName) LRowSrc = .Cells(Rows.Count, 1).End(xlUp).Row Set RngToCopy = .Range("A2:N" & LRowSrc) RngToCopy.Copy End With With ShRecompile LRowRecompile = .Cells(Rows.Count, 1).End(xlUp).Row + 1 .Range("A" & LRowRecompile).PasteSpecial xlPasteAll End With Next Iter End Sub </code></pre> <p>One major assumption is made here: that your list of sheet names start in AA1. This is important. If it doesn't, change <code>Iter = 1</code> to <code>Iter = n</code> where <code>n</code> is the row number of the true starting cell of your list.</p> <p>Let us know if this helps.</p> <p><strong>UPDATE:</strong> As the list starts in <code>AA2</code>, I have updated the above code.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload