Put data from File1 to particular sheet of File2 using Python

Question

Problem Statement:

I need to split an Excel file (File2) based on unique values in the 'Vendor Name' column and save each subset as a separate file in an existing sheet 'Data Dump' of Excel workbook (File1). However, my current implementation is inefficient and takes a lot of time.

File1 path is blank_order_book_path

File2 dataframe is df

1. Split File2 df by vendor
2. For each unique vendor, create copy of file1
3. Paste the split data to this copy of file1's sheet 'Data Dump' for all vendors
4. Save this new files in a folder

Main Part of Code:

suppliers = df['Vendor Name'].unique()
for supplier in suppliers:
    if isinstance(supplier, str):
        safe_supplier = supplier.replace('/', '_')
        supplier_df = df[df['Vendor Name'] == supplier].copy()
        wb = load_workbook(blank_order_book_path)
        ws = wb["Data Dump"]
        data = supplier_df.columns.tolist() + supplier_df.values.tolist()
        for row_idx, row in enumerate(data, 1):
            for col_idx, value in enumerate(row, 1):
                ws.cell(row=row_idx, column=col_idx, value=value)
                wb.save(f"{safe_supplier}.xlsx")
print("All files saved to Downloads Folder")

Issue:

Taking a lot of time to process. And sometimes not working.

Expectation:

I expect the code to efficiently split the Excel file by 'Vendor Name' and save each subset as a separate file within the 'Downloads/VendorSplittingApp' folder.