Copy the specified sheets in a source file to target files

copyWorkbook.py

import os
import xlwings as xw

# Paths
source_workbook_path = r"source workbook path"  # Change to your source workbook path
target_folder_path = r'targer folder'  # Change to the folder containing your target workbooks

# Names of the worksheets you want to copy
sheets_to_copy = ['Sheet1', 'Sheet2'] # Change to your sheet names

# Start an instance of Excel
app = xw.App(visible=False)

try:
    # Open the source workbook
    source_workbook = app.books.open(source_workbook_path)

    # Loop through all files in the target folder
    for file_name in os.listdir(target_folder_path):
        # Construct full file path
        file_path = os.path.join(target_folder_path, file_name)
        # Check if it's an Excel file (excluding temporary files)
        if file_name.endswith(('.xlsm', '.xlsx', 'xls')) and not file_name.startswith('~$'):
            # Open the target workbook
            target_workbook = app.books.open(file_path)

            # Copy each sheet from source to target workbook
            for sheet_name in sheets_to_copy:
                if sheet_name in [sheet.name for sheet in source_workbook.sheets]:
                    source_workbook.sheets[sheet_name].api.Copy(Before=target_workbook.sheets[0].api)
                else:
                    print(f"The sheet '{sheet_name}' was not found in '{source_workbook_path}'")

            # Save and close the target workbook
            target_workbook.save()
            target_workbook.close()

    # Close the source workbook without saving (assuming we only read from it)
    source_workbook.close
finally:
    # Quit the Excel application
    app.quit()