How to paste at the next empty cell instead of pas

Question

Im having a problem when pasting data at the recompile sheet. The recompile sheet is like a summary sheet and will paste all the data from the selected worksheet that is specify. The master sheet is a reference sheet where range ("AA") consist of all the worksheet name that i want to copy and paste to recompile sheet. Example of the worksheet name is worksheet A,B,C...etc I just need to write the worksheet name at master sheet range ("AA") and the program will track down all the worksheet name that have been specified. My problem is when i paste data from worksheet A data from worksheet B,C,D will also be pasted at the same cell causing it to be overwrite. What i am trying to do is to paste data worksheet A to recompile sheet then the next empty cell is data from worksheet B and so on. This is my code.

Private Sub CommandButton2_Click()

 Dim Sheetname, myrange As String
 Dim A, noOfrows As Integer
 Dim startRow As Integer

For i = 2 To Worksheets("Master Sheet").Cells.SpecialCells(xlCellTypeLastCell).Row

Sheetname = Worksheets("Master Sheet").Cells(i, 27).Value'Reference sheet range ("AA")
 noOfrows = Worksheets(Sheetname).Cells.SpecialCells(xlCellTypeLastCell).Row
 myrange = "A2:N" & CStr(noOfrows)'Data from column A to column N
 Worksheets(Sheetname).Select
 Worksheets(Sheetname).Range(myrange).Select
 Selection.Copy
 Sheets("Recompile").Select         
 Range("A2").Select
 ActiveSheet.Paste

 Next i
 End Sub

Answer 1

Not tested

Change this part

Sheets("Recompile").Select         
Range("A2").Select
ActiveSheet.Paste

to

With Sheets("Recompile")
   .Range("A" & .Rows.Count).End(xlUp).offset(1,0).Paste
End With

Answer 2

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 ActiveSheet and Select and that your final actions refer to only one cell (Range("A2"). This means the resultant action of each iteration changes just this cell. See below for one possible approach to this.

First, you need to iterate over the names of the worksheets found in Master Sheet, Column AA. 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 A2 to the last row of Column N is copied to the Recompile sheet, starting with Cell A2 (I'm assuming you have headers).

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.

Now, you need this to loop as many times as there are sheets. The logic of this gives rise to the following code (untested, please do in a backup copy of your workbook):

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

One major assumption is made here: that your list of sheet names start in AA1. This is important. If it doesn't, change Iter = 1 to Iter = n where n is the row number of the true starting cell of your list.

Let us know if this helps.

UPDATE: As the list starts in AA2, I have updated the above code.