Search code examples
excelvbacopyreport

Copy rows from multiple sheets to another sequentially


I am trying to write a script that takes all the rows (after the header row), and copies them sequentially into the first tab. I also have 10 tabs i would like it to do this for, and sequentially paste them into the first tab (basically pull everything from the other sheets, and put it into the first sheet like a big master sheet or report)

I tried this, but its not working

Sub Report()

page = 2
row = 1
Dim lastRow As Integer
Dim pae As Integer
Dim rw As Integer
Dim WSheet(1 To 12) As Worksheet

lastRow = Worksheets(page).Cells(Rows.Count, "A").End(xlUp).rw

    While (pge <= 12)
            While (rw <= lastRow)
        rw = rw + 1
        ws1.Rows(row).EntireRow.Copy WSheet(pge).Range("A" & lastRow)
            Wend
        pge = pge + 1
    Wend
    
End Sub

I was trying to get it to count how many rows have data, and then copy all of those rows to the first sheet before moving on to the next sheet,

It seems to skip over where i declare the variables, and then errors out on the lastrow assignment

Any help would be greatly appreciated


Solution

  • Please, try the next code:

    Sub ReportMaster()
     Dim ws1 As Worksheet, ws As Worksheet, lastRow As Long, lastER As Long, lastCol As Long
    
     Set ws1 = Worksheets(1)
    
     For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> ws1.Name Then
            lastRow = ws.Range("A" & ws.rows.count).End(xlUp).row
            lastCol = ws.cells(1, ws.Columns.count).End(xlToLeft).Column
            lastER = ws1.Range("A" & ws.rows.count).End(xlUp).row + 1 'last empty row
            ws.Range(ws.Range("B1"), ws.cells(lastRow, lastCol)).Copy ws1.Range("A" & lastER)
        End If
     Next ws
    End Sub