I have something that does exactly what you are asking for, if you want to copy multiple workbooks I suggest creating a new worksheet to capture the workbook information onto a spreadsheet. Instructions below
Create a new worksheet and give it a name, in this case we'll call the sheet 'Control'
Create a new module in VBA and use the code below to operate the workbook copy
I have left a section out for you to write your code for the functions that you want to perform.
Sub WorkbookConsolidator()
Dim WB As Workbook, wb1 as workbook
Dim WBName as Range
Dim folderselect as Variant, wbA as Variant, wbB as Variant,
Dim I as long, J as long
Dim objFolder As Object, objFile As Object
Dim WBRange as String
'Set Core Variables and Open Folder containing workbooks.
Set WB = ThisWorkbook
Worksheets("Control").Activate
Set FolderSelect = Application.FileDialog(msoFileDialogFolderPicker)
FolderSelect.AllowMultiSelect = False
MsgBox ("Please Select the Folder containing your Workbooks")
FolderSelect.Show
WBRange = FolderSelect.SelectedItems(1)
Set objFolder = objFSO.GetFolder(FolderSelect.SelectedItems(1))
' Fill out File name Fields in Control Sheet
' The workbook names will be captured in Column B
' This allows allocation for up to 100 workbooks
For I = 1 To 100
For Each objFile In objFolder.files
If objFile = "" Then Exit For
Cells(I, 2) = objFile.Name ' Workbook Name
Cells(I, 3) = WBRange ' Workbook Path
I = I + 1
Next objFile
Next I
'Loop through the list of workbooks created in the 'Control' Directory, adjust the loop range as preferred
For J = 100 To 1 Step -1
With Workbooks(ThisWorkbook).Worksheets("Control")
BookLocation = .Range("C" & J).Value
BookName = .Range("B" & J).Value
End With
Set wb1 = Workbooks.Open(Booklocation & Bookname)
' Write your code here'
CleanUp:
wb1.Close SaveChanges:=False
Next J
End Sub()
`