I have 5 files that are downloaded to \Downloads
Each file is a unique name_MMDDYYYY
Sheet0 is where the data is on each file
I'd like to copy Range("A3:S" & .Range("A" & Rows.Count).End(xlUp).Row) from each of the 5 files
And paste them in no particular order into a new file
As an example - If I wanted to work on this today, I would download the following files
MyField_87_87STX_LA_10192021.xls
MyField_84_84Atlanta_10192021.xls
MyField_83_83DC_MD_NC_SouthernVA_10192021.xls
MyField_82_82Florida_10192021.xls
MyField_80_80NTX_OK_10192021.xls
Using an old MrExcel thread - Copy range from one workbook to another and throwing in an input box I was able to create this:
What I need help with is
How can I loop through with each of the file names? I assume using an Array.
knowing to paste data from each sheet under the previous set of pasted data in Sheet1 of the new blank file.
Closing the files data is copied from after paste
Each file is a unique name_MMDDYYYY
Sheet0 is where the data is on each file
I'd like to copy Range("A3:S" & .Range("A" & Rows.Count).End(xlUp).Row) from each of the 5 files
And paste them in no particular order into a new file
As an example - If I wanted to work on this today, I would download the following files
MyField_87_87STX_LA_10192021.xls
MyField_84_84Atlanta_10192021.xls
MyField_83_83DC_MD_NC_SouthernVA_10192021.xls
MyField_82_82Florida_10192021.xls
MyField_80_80NTX_OK_10192021.xls
Using an old MrExcel thread - Copy range from one workbook to another and throwing in an input box I was able to create this:
VBA Code:
Sub Macro1()
Dim myDate As Long
myDate = (InputBox("Begining Date"))
Set wb2 = ActiveWorkbook
Set wb1 = Workbooks.Open("C:\Users\krichmp\Downloads\myfield_87_87STX_LA_" & myDate & ".xls")
wb1.Worksheets("Sheet0").Range("A3:S" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Application.DisplayAlerts = False
' Copy to last row of data + 1 (can't get this to work)
wb2.Sheets("Sheet1").Range("A1:S" & wb2.Sheets("Sheet1").Range("A" & Rows.Count).End(xlDown).Row).PasteSpecial
Application.CutCopyMode = False
Range("A1").Select
' close each file data is copied from (can't get this to work)
Workbooks("C:\Users\krichmp\Downloads\myfield_87_87STX_LA_" & myDate & ".xls").Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub
What I need help with is
How can I loop through with each of the file names? I assume using an Array.
knowing to paste data from each sheet under the previous set of pasted data in Sheet1 of the new blank file.
Closing the files data is copied from after paste