VBA – Move Sheets to Closed Workbooks if Sheet Names Partially Match Closed Workbook Names

[leever]

Hello. I have 1 open workbook with multiple sheets.

Open Workbook Name: Source_Workbook
Source_Workbook Sheets: [Economy_AA] [Economy_BB] [Economy_CC] [Economy_DD] [Economy_EE]

I also have 5 closed workbooks in a folder with the following file names and sheets:

"File_AA_Vancouver.xlsx" --> [Population_AA] [Weather_AA]
"File_BB_Toronto.xlsx" --> [Population_BB] [Weather_BB]
"File_CC_Calgary.xlsx" --> [Population_CC] [Weather_CC]
"File_DD_Ottawa.xlsx" --> [Population_DD] [Weather_DD]
"File_EE_Montreal.xlsx" --> [Population_EE] [Weather_EE]

I want to move each sheet in Source_Workbook to each of the files only if there is a partial match on sheet name and file name.

[Economy_AA] move to "File_AA_Vancouver.xlsx"
[Economy_BB] move to "File_BB_Toronto.xlsx"
[Economy_CC] move to "File_CC_Calgary.xlsx"
[Economy_DD] move to "File_DD_Ottawa.xlsx"
[Economy_EE] move to "File_EE_Montreal.xlsx"

I would also like the [Economy_##] sheet to go before the [Weather_##] sheet.

Is there a macro/code that can accomplish this instead of me moving each sheet one by one to the appropriate files. Any help would be greatly appreciated.

Thank you.

 

[leever]

This is the code I have written which works but is going to be repetitive when there are more sheets and files. I am looking for a dynamic way to get this done based on sheet name and file name. Thank you in advance.

---
Sub command()

Dim wb As Workbook

ThisWorkbook.Worksheets("Economy_AA").Select
ThisWorkbook.Worksheets("Economy_BB").Select
ThisWorkbook.Worksheets("Economy_CC").Select
ThisWorkbook.Worksheets("Economy_DD").Select
ThisWorkbook.Worksheets("Economy_EE").Select

Selection.Copy

Set wb = Workbooks.Open("C: My Folder\File_AA_Vancouver.xlsx")
ThisWorkbook.Worksheets("Economy_AA").Copy Before:=wb.Worksheets("Weather_AA")
wb.Close SaveChanges:=True
Set wb = Nothing

Set wb = Workbooks.Open("C: My Folder\File_BB_Toronto.xlsx")
ThisWorkbook.Worksheets("Economy_BB").Copy Before:=wb.Worksheets("Weather_BB")
wb.Close SaveChanges:=True
Set wb = Nothing

Set wb = Workbooks.Open("C: My Folder\File_CC_Calgary.xlsx")
ThisWorkbook.Worksheets("Economy_CC").Copy Before:=wb.Worksheets("Weather_CC")
wb.Close SaveChanges:=True
Set wb = Nothing

Set wb = Workbooks.Open("C: My Folder\File_DD_Ottawa.xlsx")
ThisWorkbook.Worksheets("Economy_DD").Copy Before:=wb.Worksheets("Weather_DD")
wb.Close SaveChanges:=True
Set wb = Nothing

Set wb = Workbooks.Open("C: My Folder\File_EE_Montreal.xlsx")
ThisWorkbook.Worksheets("Economy_EE").Copy Before:=wb.Worksheets("Weather_EE")
wb.Close SaveChanges:=True
Set wb = Nothing


End Sub