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