Excel copy data from different workbooks into 1 workbook

Question

I am trying to paste data from different workbooks into 1 master workbook. So far the copy and pasting of data is working, but, when i paste the data into the workbook, there are rows being skipped after each workbook is being pasted into the master workbook. The picture below shows the problem.

2,3 and 6-12 are being skipped. Below is my code:

    Sub Macro1()
'
' Macro1 Macro
'
    Dim wb1 As Workbook
    Set wb1 = ThisWorkbook

Path = "C:\Users\Tester\Documents\test\"
Filename = Dir(Path & "*.xls")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Rows("2:" & Range("A1").End(xlDown).Row).copy _
        wb1.Sheets(1).Range("A" & Range("A1").End(xlDown).Row + 1)
    Application.CutCopyMode = False
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

I think the problem has something to do with this line "wb1.Sheets(1).Range("A" & Range("A1").End(xlDown).Row + 1)" but i am not sure how to fix this. Any suggestions? Thank you!

See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for the right way to find the last row. — BigBen, Apr 02 '20 at 01:08
Try wb1.Sheets(1).Range("A" & Range("A1").End(xlDown).Row + 1) -> wb1.Sheets(1).Range("A" & wb1.Sheets(1).Range("A1").End(xlDown).Row + 1). — ManhND, Apr 02 '20 at 01:14
you are working on open filename so Range("A1").End(xlDown).Row mean last row of open filename in active sheet. You just need to specify for vba that what file you want to find last row — ManhND, Apr 02 '20 at 01:21
@ManhND it gave me a Runtime error 1004 application-defined or object-defined error. I specified the file i want to find by setting wb1 which is Thisworkbook, the master workbook. — Ruiru, Apr 02 '20 at 01:42
You only define the range you want to copy to. But inside it Range("A1").End(xlDown).Row the file is not specified yet, so excel will get the active file. try to change you destination to wb1.Sheets(1).Range("A" & wb1.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1) — ManhND, Apr 02 '20 at 02:09
Sheet.Rows("2:" & Range("A1").End(xlDown).Row).copy _ wb1.Sheets(1).Range("A" & wb1.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1) It'll look like that — ManhND, Apr 02 '20 at 02:10
@ManhND may you write your comment as an answer, so Ruiru can mark this question as solved ? — Vincent, Apr 02 '20 at 08:05

score 0 · Accepted Answer · answered Apr 02 '20 at 08:28

You only define the range you want to copy to, but inside it command Range("A1").End(xlDown).Row the file is not specified yet, so excel will get form active file.

Try to change you destination to

wb1.Sheets(1).Range("A" & wb1.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1)

Your code will look like

Sheet.Rows("2:" & Range("A1").End(xlDown).Row).copy 
     _ wb1.Sheets(1).Range("A" & wb1.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1)

Excel copy data from different workbooks into 1 workbook

1 Answers1