How to migrate many excel sheets to one master workbook

Question

I commonly have several workbooks with individual sheets to migrate to a master workbook, think:

Jan.xlsx
Feb.xlsx
Mar.xlxs
...
Dec.xlsx

to a recap on a single sheet named 2020Recap.xlxm. I'd like to build a macro to collect each worksheet and write to the recap workbook, I've started with the VBA script below.

I'm having trouble with the VBA below

I'd like to also add to the macro to use the filename as the sheet name.

Sub CopySheets()

Workbooks("C:\Test\Account AR Aging Patient.xlsx").Sheets("Account AR Aging Patient.xlsx").Copy _
  After:=Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets(Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets.Count)

Workbooks("C:\Test\Account AR Aging Payer.xlsx").Sheets("Account AR Aging Payer.xlsx").Copy _
    After:=Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets(Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets.Count)

Workbooks("C:\Test\AR History.xlsx").Sheets("AR History").Copy _
    After:=Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets(Workbooks("c:\Test\MEBIllingOffice.xlsm").Sheets.Count)

End Sub

Have a look at this: https://stackoverflow.com/q/30575923/4961700 and vote for it if you find it useful. — Solar Mike, Aug 16 '21 at 19:50
On the very first line, it seems to be the file and directory name — JL Thalacker, Aug 16 '21 at 20:25

score 2 · Accepted Answer · answered Aug 16 '21 at 21:41

You have the workbook name in the sheet name. Try:

Dim wb_source As Workbook
Dim wb_target As Workbook

Application.DisplayAlerts = False

Set wb_source = Workbooks.Open("C:\Test\Account AR Aging Patient.xlsx")
Set wb_target = Workbooks.Open("c:\Test\MEBIllingOffice.xlsm")

'assuming the sheet name is "Sheet1"
wb_source.Sheets("Sheet1").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
wb_source.Close

'next source.  note: sheet names must be unique within target
Set wb_source = Workbooks.Open("C:\Test\Account AR Aging Payer.xlsx")
wb_source.Sheets("Sheet2").Copy after:=wb_target.Sheets(wb_target.Sheets.Count)
wb_source.Close

wb_target.Save
wb_target.Close

Application.DisplayAlerts = True

How to migrate many excel sheets to one master workbook

1 Answers1