VBA copy data from multiple files into 1 file

Question

I am looking to loop through excel files within a given folder (These files are updated during the morning) and extract data into a new excel file and save it.

There are about 10 files on average to copy data from.

My question is: What would the loop code look like? Any help would be appreciated to get me started in the right direction. This is for work, just trying to improve processes.

Under "related" on the right of this page there are a list of suggested previous questions - I would start there — Tim Williams, Feb 22 '19 at 23:40

score 0 · Accepted Answer · answered Feb 22 '19 at 23:39

Sub Combine_workbooks()
Path = "C:\Users\PChen\Desktop\Test\"
FileName = Dir(Path & "*.xlsx")
Workbooks.Add
  Do While FileName <> ""
  Workbooks.Open FileName:=Path & FileName, ReadOnly:=True
  Call AuthOpenAll
  Worksheets("Data").Activate
  Worksheets("Data").Copy After:=Workbooks("book1").Worksheets("sheet1")
     Workbooks(FileName).Close savechanges:=False
     FileName = Dir()
  Loop
End Sub

}

VBA copy data from multiple files into 1 file

1 Answers1