How to Use VBA to Split Two Excel Sheets into Region-Based Workbooks
Question details
The user wants to separate data from two different Excel worksheets into individual workbooks for each region using a VBA macro.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating the distribution of regional data stored across two different sheets into separate, localized workbooks.
- Observed behavior
- Currently, the data is combined in two main sheets. The goal is to generate one distinct Excel workbook per region, containing the filtered data from both original sheets.
Ensure that the region column names and data formats are identical across both source sheets to prevent filtering errors during the macro execution.
Create a Custom VBA Macro to Filter and Save Regional Workbooks
Write a VBA procedure that extracts unique regions, creates individual workbooks, copies matching rows from both sheets, and saves the new files.
To achieve this, the VBA code must first identify all unique regions from both sheets to determine how many workbooks need to be generated. It then loops through these regions, filtering both source sheets simultaneously.
During the loop, a new workbook is generated. The filtered, visible rows from both source sheets are copied and pasted into the new workbook before it is saved with a region-specific file name.
Press 'ALT + F11' in your spreadsheet program to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' from the top menu, then select 'Module' to create a blank script window.
Set up a Dictionary object or use the Advanced Filter function within your script to compile a list of unique region names from your specified region column.
Create a 'For Each' loop that goes through every unique region. Inside the loop, apply 'AutoFilter' on the designated region column for both source sheets.
Inside the loop, use 'Workbooks.Add' to generate a new file. Use 'SpecialCells(xlCellTypeVisible).Copy' on the filtered source sheets, and paste the data into the new workbook's sheets.
Use 'ActiveWorkbook.SaveAs' specifying your output folder path and the region name as the filename. Close the workbook automatically using 'ActiveWorkbook.Close False' to proceed to the next region.
Automate Spreadsheet Tasks Using Macros in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, allowing you to run, edit, and create advanced automation scripts seamlessly. You can execute custom code to split data, generate multiple workbooks, and automate repetitive tasks just like in Microsoft Excel.
- 1. Enable Macros: Open your macro-enabled file (.xlsm) in WPS Spreadsheet and click 'Enable Macros' if prompted by the security warning.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click 'Macros' or 'Visual Basic' to view your scripts.
- 3. Run the VBA Script: Select your macro for splitting the sheets by region and click 'Run' to instantly generate the individual workbooks.
Frequently Asked Questions
Why is my VBA script copying hidden rows instead of only the filtered visible rows?
To ensure you only copy filtered data, you must apply the `SpecialCells(xlCellTypeVisible)` method to your range object in the VBA script before executing the `Copy` command.
Can I save the newly created region workbooks as specific file types using VBA?
Yes. When using the `ActiveWorkbook.SaveAs` command, you can specify the `FileFormat` property. For example, use `xlOpenXMLWorkbook` for standard .xlsx files or `xlOpenXMLWorkbookMacroEnabled` for .xlsm files.
How do I dynamically define the output folder path for the split workbooks?
You can use `ThisWorkbook.Path` in your VBA code to automatically save the new workbooks in the exact same directory as your master workbook.
What if one of the sheets doesn't contain data for a specific region?
You should add an 'If' statement checking the visible row count after applying the filter. If the count indicates only the header row is visible, instruct the script to skip the copy-paste sequence for that specific sheet.
