I have multiple worksheets (for this example say Sheet1, Sheet2, Sheet3) with same number of columns, but different row counts (changes constantly).
I want to consolidate all of these on one sheet.
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) - 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 paste Sheet1 (the first sheet) into Consolidation, it does start in Row 2 but it also pastes blank rows to fill the entire worksheet to 1,048,576 rows (limit of sheet).
How do I find the first blank row, and then paste Sheet2 data (from Row 2) 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
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