ajjava
06-05-2019, 01:55 PM
I have the following code that merges several Excel workbooks into one (new) destination workbook.
Each workbook to be merged contains several sheets. The sheets have identical names, across all of the workbooks to be merged.
SO, I'd like the macro to give the merged sheets a NEW, identifying name, as they are copied into the destination file.
For instance, the original sheet name is "CAT", from original workbook named "ANIMALS 2018 WORLD.XLS".
I want the new sheet, in the destination file, to be named "2018-CAT" (the '2018' being the identifier that tells me what the source file was).
Possible?
Sub CombineWorkbooks()Path = "J:\CPAT Process\Excel staging\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Each workbook to be merged contains several sheets. The sheets have identical names, across all of the workbooks to be merged.
SO, I'd like the macro to give the merged sheets a NEW, identifying name, as they are copied into the destination file.
For instance, the original sheet name is "CAT", from original workbook named "ANIMALS 2018 WORLD.XLS".
I want the new sheet, in the destination file, to be named "2018-CAT" (the '2018' being the identifier that tells me what the source file was).
Possible?
Sub CombineWorkbooks()Path = "J:\CPAT Process\Excel staging\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ActiveWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub