Loop through Excel Files in Folder and Append to Macro Workbook

Question

I have a need to open up all the .xlsx files in a folder and copy their contents into the workbook that is running the macro. I have gotten so far as being able to loop through all the Excel files in a folder thanks to this blog post, with one change in the code from myExtension = "*.xls" to myExtension = "*.xlsx". However I can't figure out how to copy all the contents of worksheet 1 of each workbook and paste it into ThisWorkbook which is the one running the macro.

For instance, in the Do While loop, the code in the blog post above does this:

'Change First Worksheet's Background Fill Blue
  wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)

But I want to do something like this:

'Copy all the cells that have data in them and paste/append to VBA workbook
  wb.Worksheets(1).UsedRange.Copy ' this hasn't been working for me
  ThisWorkbook.Worksheets(1). (somehow get to the first empty row) . Paste

Any ideas?

By the way, the Excel files that I'm copying from will all have their data in the first sheet and they will not have any header rows.

Please specify "this hasn't been working for me"; does it produce an error or something? — vacip, Jun 17 '16 at 15:03
Also, check out this post to [learn how to avoid using select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and copy-paste for that matter... — vacip, Jun 17 '16 at 15:04
And [this](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) will tell you how to find the last used cell. — vacip, Jun 17 '16 at 15:04
@vacip Well, I've created an excel file with "some data" in the A1 cell and put it in my target folder. Then when I run the macro, nothing ends up on my clipboard. — , Jun 17 '16 at 15:16

score 0 · Answer 1 · answered Jun 17 '16 at 15:13

iRow_Target = 0
(For Each Workbook in your directory)
Set Wb = Workbooks.Open(Wb_File_Name)
For Each Ws in Wb.Sheets
    For iRow = 1 to Ws.UsedRange.Rows.Count 'This gives you rows count
        Ws.Rows(iRow).Copy 
        iRow_Target = iRow_Target + 1 'This will have the last row saved
        Thisworkbook.Sheets("Target").Range("A" & iRow_Target).xlPasteAll 'You can use other xlPaste<options> based on your needs
    Next
Next

PaichengWu · Answer 2 · 2016-06-17T15:30:53.220

0

with thisworkbook.worksheets(1)
  wb.worksheets(1).usedrange.copy .cells(.rows.count,1).end(xlup).offset(1)
end with

edited Jun 17 '16 at 15:30

answered Jun 17 '16 at 15:19

PaichengWu

2,649
1
14
28

This looks like it should work without a problem, but it isn't working for me. In debugging, the program opens the target file, gets to this point in the code, and then nothing is ever actually copied over. Not sure why. – Jun 17 '16 at 15:40

Loop through Excel Files in Folder and Append to Macro Workbook

2 Answers2