VBA & Macro Problems

How to Create Supplier-Specific Excel Workbooks from a Template Using VBA

Muhammad Talha Sep 30, 2026 869 views

Question details

The user needs to automate the process of creating individual Excel files for distinct suppliers by copying a template worksheet and transferring matching data rows from a master sheet.

How to Create Supplier-Specific Excel Workbooks Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Automating the distribution of supplier-specific data by splitting a master dataset into separate workbook files based on a pre-defined template.
Observed behavior: A VBA solution is required to identify unique suppliers, duplicate a template into new workbooks, extract the relevant rows including data validation, and save each file with the supplier's name.

Before you start

Ensure your source data is organized with clear column headers, a dedicated 'Template' worksheet exists in your active file, and the Developer tab is enabled to access the VBA Editor.

Solution 1Recommended

Use a VBA Macro to Split Data into Supplier Workbooks

Implement a VBA script that loops through source data, filters by unique supplier, generates a new workbook from a template, and transfers the corresponding rows.

This macro automates the repetitive task of separating data. It identifies unique supplier names in your designated column, generates a new workbook for each supplier, copies your predefined template sheet, and transfers the filtered data rows while maintaining cell formatting and data validation.

Open the VBA Editor

Launch your Excel workbook containing the master data and template. Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click on 'Insert' from the top menu and select 'Module' to create a blank script window.

Write the Data Splitting Script

Write or paste a VBA script that utilizes a Collection or Dictionary to find unique supplier names. Instruct the script to loop through these names and apply an AutoFilter to the source data.

Copy Template and Filtered Data

Within the loop, add code to copy the 'Template' sheet to a new workbook (`ThisWorkbook.Sheets("Template").Copy`). Then, copy the visible filtered rows from your source sheet and paste them into the newly created template sheet.

Save the Supplier Workbooks

Use `ActiveWorkbook.SaveAs` to save each new file dynamically by appending the supplier's name to your folder path, then close the workbook before the loop proceeds to the next supplier.

Use a VBA Macro to Split Data into Supplier Workbooks

Customize Your VBA Parameters: Remember to update the VBA script with your specific worksheet names, the correct column letter for suppliers, and a valid destination folder path for saving the newly created files.

Automate Workflows with WPS Office

Use WPS Office to Run VBA Macros and Automate Excel Tasks

WPS Office provides excellent support for VBA macros, allowing you to easily run scripts that automate repetitive tasks, such as splitting master datasets into supplier-specific workbooks, just as you would in Microsoft Excel.

1. Open Your Macro-Enabled Spreadsheet: Download and launch WPS Office, then open the spreadsheet that contains your master data and template.
2. Access the Developer Tools: Navigate to the 'Tools' tab on the ribbon and click on 'Developer' to reveal the macro and VBA options.
3. Open the VBA Editor: Click on 'Visual Basic' or press Alt + F11 to launch the WPS VBA Editor.
4. Run the Supplier Script: Insert a new module, paste your data-splitting VBA code, and press F5 to automatically generate your supplier-specific workbooks.

Fully compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsx, .xls)Native support for VBA macros to automate complex workflows and data transfersAdvanced filtering and worksheet management features in an intuitive interfaceLightweight software that runs efficiently on Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Can the VBA macro preserve data validation when creating the new workbooks?

Yes. When the macro copies the template worksheet or transfers data using the standard `Range.Copy` method, the data validation rules and dropdown lists are preserved in the newly generated supplier workbooks.

How do I save the generated workbooks in a specific desktop folder?

You can define a specific folder path in your VBA code using a string variable (e.g., `MyPath = "C:\Users\Name\Desktop\Suppliers\"`) and append the supplier string to it during the `SaveAs` command.

What happens if a supplier name contains invalid characters for a file name?

If a supplier name contains characters like slashes (/), asterisks (*), or question marks (?), the `SaveAs` operation will fail. You should include a replacement function in your macro to strip invalid characters from the supplier string before saving.

How does the VBA macro identify unique suppliers from the master list?

A common VBA method involves adding all supplier names to a `Collection` or `Dictionary` object, which naturally filters out duplicates. Alternatively, you can use the Advanced Filter feature via VBA to copy unique records to a temporary column.

Excel & Data Problems