Excel/Macro to copy data from different workbooks to one workbook
I have folders created basis each day in a month in this format "YYYYMMDD". Each folder has four worbooks called as 1,2,3,4. A VBA code is required
to copy data from all four wokbooks(1,2,3,4) and should copy paste data(used range) in a new workbook in four different worksheets(sheet1, Sheet2, sheet3, sheet4).
Appreciate your help in this regard .
in this macro, I assume you have four workbooks
each with a single worksheet in them. I copy the worksheet to a single new workbook.
You get a dialog to select whichever directory you want to process.
Dim fldr As FileDialog
Dim sItem As String
Dim bk As Workbook, bkMaster As Workbook
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
sItem = .SelectedItems(1)
Set bkMaster = ActiveWorkbook
If Right(sItem, 1) <> "\" Then sItem = sItem & "\"
v = Array("1", "2", "3", "4")
For i = LBound(v) To UBound(v)
Workbooks.Open sItem & v(i) & ".xlsx"
Set bk = ActiveWorkbook
Application.DisplayAlerts = False
Application.DisplayAlerts = True
that should get you started.
It was tested with the stated assumptions and worked fine for me.