Dynamic Range Paste Problem within a Loop of spreadsheets in a directory - Excel 2013

I am looping through a directory of spreadsheets to copy and paste variable rows (set column range) to a master spreadsheet. I am having a problem with one line of code - the destination paste part. Each spreadsheet in the directory will open and copy and paste but I can't get the pasting of subsequent spreadsheets to paste further down the master sheet. It just overlays the data for every spreadsheet until the end of the directory list.

.Range(("A2:AZ") & Range("B" & Rows.Count).End(xlUp).Row).Copy _
Dataset.Range("A" & (emptyRow)).End(xlUp).Offset(1, 0)

emptyRow (Integer) is the row number of the next empty row in the master spreadsheet (it is correct as I have tested this).

Thanks in advance.

You said you tested it. What did you test?
Test this and see what you get.

Please Login or Register  to view this content.

We can't see what the empty row is as you only show a couple of your code lines here but I assume it is the first empty cell in Column A
Try

Please Login or Register  to view this content.

or something similar.

I am really sorry, I failed to mention the first row of the source data is a header and will not be brought forward to the destination spreadsheet. Below is the complete section of code:

emptyRow = (Dataset.Range("B" & Rows.Count).End(xlUp).Row) + 1
rowCount = sourceData.Range("B" & Rows.Count).End(xlUp).Row

If sourceData.cells(rowCount, 2) > 1 Then
'Loop through Source - paste to Master
With sourceData
If rowCount > 2 Then
.cells.EntireRow.Interior.ColorIndex = 0
.cells.EntireRow.Borders.LineStyle = xlNone
Debug.Print Dataset.Rows.Count
.Range(("A2:AZ") & Range("B" & Rows.Count).End(xlUp).Row).Copy _
Dataset.cells(Dataset.Rows.Count, 2).End(xlUp).Offset(1, 0)
' Dataset.Range("A" & (emptyRow)).End(xlUp).Offset(1, 0)
Else
.cells.EntireRow.Interior.ColorIndex = 0
.cells.EntireRow.Borders.LineStyle = xlNone
.Range(("A2:AZ") & Range("B" & Rows.Count).End(xlUp).Row).Copy _
Dataset.cells(Dataset.Rows.Count, 2).End(xlUp).Offset(1, 0)
' Dataset.Range("A" & (emptyRow)).End(xlUp).Offset(1, 0)
End If
'Application.CutCopyMode = False
End With
End If

Almost - made a tweak to your code and now it works beautifully.

Please Login or Register  to view this content.