CODEWITHSUNDEEP
excelvba
Ruiru
Ruiru

Reputation: 117

Excel copy data from different workbooks into 1 workbook

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.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!

Upvotes: 0

Views: 105

Answers (1)

ManhND
ManhND

Reputation: 77

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)

Upvotes: 0

Related Questions