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

Siddharth Rout · Accepted Answer · 2012-06-04 09:45:48Z

1

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.

edited Jun 4, 2012 at 9:45

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Jul 20, 2010 at 17:26

Cynicszm

961 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

EKet Over a year ago

I'm a bit new to vba, but if I understand correctly the Range("A2") is never null or empty. there's always data from A2:Jxxxx which needs to be populated into the master. Thanks

Cynicszm Over a year ago

I would check the data on sheet 3 to be sure that Range A2 onwards is always populated, can't see anything else that would cause the problem from your vba...especially if it works for other sheets

EKet Over a year ago

Yea that's interesting, sheet3 is populated from A2:J903. I thought maybe there's a size issue where the Range object or Worksheet object can't hold more than a certain number of rows. But obviously that's dumb and other sheets work fine with more data. I guess it's one of those mysteries because I can't think of anything else at this point. Thanks for the help tho.

EKet Over a year ago

there was an empty cell at A843...guess I missed it. That was the solution. Thanks Cynicszm

Collectives™ on Stack Overflow

Excel VBA Copy multiple sheets into one sheet not working

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related