Is there a way to combine Excel workbooks into one Master workbook within python?

I would like to know if this is possible without merging all Excel workbooks into a single Worksheet within a Master workbook

(rather)

A Master workbook, containing the sheets of all the slave workbooks

This picture shows what I'd like to do

Perhaps is there a way to use VBA script within python to do this?

Yes, this is achievable using python and pandas. You can open multiple files by sheet and simply write them to a single output document. See the documentation here for more information.

You'll need to change the variable names to fit your use case, but here is a generic idea:

mon_sheet = pd.read_excel('file_1_path.xlsx', sheetname = 'Mon')
tue_sheet = pd.read_excel('file_1_path.xlsx', sheetname = 'Tue')
wed_sheet = pd.read_excel('file_1_path.xlsx', sheetname = 'Wed')
thu_sheet = pd.read_excel('file_2_path.xlsx', sheetname = 'Thu')
fri_sheet = pd.read_excel('file_2_path.xlsx', sheetname = 'Fri')
sat_sheet = pd.read_excel('file_3_path.xlsx', sheetname = 'Sat')
sun_sheet = pd.read_excel('file_3_path.xlsx', sheetname = 'Sun')

with pd.ExcelWriter('output.xlsx') as writer: 
	mon_sheet.to_excel(writer, sheet_name = 'Mon')
	tue_sheet.to_excel(writer, sheet_name = 'Tue')
	wed_sheet.to_excel(writer, sheet_name = 'Wed')
	thu_sheet.to_excel(writer, sheet_name = 'Thu')
	fri_sheet.to_excel(writer, sheet_name = 'Fri')
	sat_sheet.to_excel(writer, sheet_name = 'Sat')
	sun_sheet.to_excel(writer, sheet_name = 'Sun')