VBA & Macro Problems

How to Create Separate Excel Worksheets for Each Vendor Using VBA

Chanuka Geekiyanage Sep 27, 2026 869 views

Question details

The user needs to automate the process of splitting an Excel product list into separate worksheets for each vendor.

How to Create Separate Excel Worksheets for Each Vendor Using VBA

Product: Excel
Device & OS: not provided
Scenario: Organizing a large product list by splitting it into multiple sheets based on the vendor name located in column A.
Observed behavior: Currently, all products are housed in a single sheet. The goal is to distribute the data into individual vendor-specific worksheets while preserving the header row.

Before you start

Before running the macro, ensure that the vendor names in column A do not contain invalid characters for sheet names (such as \, /, ?, *, [, or ]) and that your data has a header row in row 1.

Solution 1Recommended

Use a Custom VBA Macro to Split the Data

Run a VBA script that automatically loops through column A, creates new sheets for unique vendors, and copies the corresponding rows.

This VBA macro efficiently handles the data splitting process. It checks if a worksheet for the vendor already exists. If not, it creates one, names it after the vendor, and copies the header row before pasting the product data.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel or WPS Spreadsheet.

Insert a New Module

In the left-hand Project Explorer pane, right-click your workbook name, select 'Insert', and then choose 'Module'.

Paste the VBA Code

Copy your SplitData macro code and paste it into the blank module window. Ensure the script correctly targets column A for reading vendor names.

Execute the Macro

Close the VBA Editor. Press Alt + F8 to open the Macro dialog, select 'SplitData' from the list, and click 'Run'.

Macro Security Settings: Make sure macros are enabled in your Trust Center settings before attempting to run the script. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain the code.

Advanced Spreadsheet Automation

Automate Data Splitting with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to easily automate complex tasks like dividing vendor lists into separate worksheets without hassle.

1. Open Your Spreadsheet: Launch WPS Office and open the file containing your vendor product list.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'VB Editor'.
3. Run the Script: Insert a module, paste your SplitData VBA code, and run it to instantly create separate sheets for each vendor.

Seamlessly execute VBA macros to split data into multiple worksheets.High compatibility with Microsoft Excel VBA scripts and formatting.Process large datasets quickly with a lightweight and robust application.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting an error when the macro tries to create a worksheet?

This usually happens if a vendor name in column A contains special characters that are not allowed in worksheet names, such as asterisks (*), question marks (?), or slashes (/). Ensure all vendor names use valid alphanumeric characters.

How can I change the column used to split the data?

In the VBA code, change the column reference in sourceSheet.Cells(s, "A") to the letter of the column you want to use. For example, replace "A" with "B" to split the data based on column B.

What happens if a worksheet for a vendor already exists?

The macro is designed to check for existing worksheets first. If it finds one matching the vendor name, it skips creating a new sheet and simply appends the new data to the next available empty row in that existing sheet.

Can I run this macro on a Mac?

Yes, as long as your version of Excel for Mac supports VBA. Alternatively, WPS Office provides excellent macro support for cross-platform spreadsheet automation.

Excel & Data Problems