Excel VBA Copy multiple sheets into one sheet not working

Question

I have the following vba: Data is all text, Rows A - J with column headers on every sheet are the same Data is sql queries all with "top 1000" 4 sheets (sheet1, sheet2, sheet3, Master)

sheet 1: 100 rows sheet 2: 34 rows sheet 3: 900 rows Master: merged data from 3 sheets

PROBLEM: Sheet3 only copies 84 rows specifically however adding more rows to other sheets will copy over to Master. Only sheet3 will not copy more than 84 rows.

' Step 1: Clear master before updating ' Step 2 : Loop through the regional sheets

Sub Consolidate()

Dim cell            As Range
Dim wks             As Worksheet

Sheets("Master").Range("A2:Z65536").ClearContents

For Each wks In ThisWorkbook.Worksheets
    If wks.Name <> "Master" And wks.Range("A2") <> "" Then

        For Each cell In wks.Range(wks.Range("A2"), wks.Range("A2").End(xlDown))
             cell.EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)
        Next cell
    End If
Next wks

End Sub

Thanks

Cynicszm Cynicszm · Accepted Answer · 2010-07-20T17:26:15

Is the data starting in Range(A2) always populated?

The For Each cell In wks.Range(wks.Range("A2"), wks.Range("A2").End(xlDown)) will start from A2 and go to the last populated cell before a blank/empty cell.

Excel VBA Copy multiple sheets into one sheet not working

1 Answers