Excel VBA Copy and Paste Multiple Sheets, Count Rows not working-CodePudding

I'm confused as why this is not working properly: I have multiple worksheets (for this example say Sheet1, Sheet2, Sheet3) with same number of columns on each, but different row counts (changes constantly). I want to consolidate all of these on one sheet, in order as I go.

The headings on the Consolidation sheet have been pre-set and so I am starting to paste into Row 2 of that sheet.

Sheet1 = 5000 rows (inc header row) - confirmed, there are no blank rows at bottom

Sheet2 = 300 rows (inc header row) - ditto

Sheet3 = 1200 rows (inc header row) - ditto

Consolidation = 1 row (header row only) - ditto

When I use the following coding to Paste Sheet1 (the first sheet) into Consolidation, it does paste in all rows except the header row - as required, starting in Row (A)2. BUT: It also pastes in blank rows to fill the entire worksheet to 1,048,576 rows (limit of sheet)??!!

Not sure why this is occurring, or how to get around these dummy blank rows tagging along. Not entirely sure how to find the last blank row, and then paste Sheet2 data (from Row2) onto the bottom, and continue with similar for next sheet.


Sheets("Sheet1").Range("A2:CF" & Rows.Count).Copy
Sheets("Consolidate").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Any help gratefully received. Thanks in advance.

CodePudding user response：

You aren't specifying the last row to do the copy paste. Try something like that is, and try to avoid using select.

Sub CheckDuplicateAcrossWorkbook()
Dim pullWs As Worksheet, dropWS As Worksheet, lastRow As Long

Set pullWs = Sheets("Sheet1")
Set dropWS = Sheets("Consolidate")

'make sure no rows are hidden)
lastRow = pullWs.Cells(Rows.Count, 1).End(xlUp).Row

pullWs.Range("A2:CF" & lastRow).Copy
dropWS.Range("A2").Paste
Application.CutCopyMode = False


End Sub