How to Split Data into Workbooks Using Two Filters via VBA Macro
Question details
The user requires an Excel VBA macro to automatically divide imported data into individual workbooks by filtering two separate columns and saving the outputs to a specific directory.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the separation of large datasets into individual files based on a primary filter value in column E and a secondary filter value in column DF.
- Observed behavior
- The correct macro logic processes nested AutoFilter operations, copies the visible rows with formatting, and saves each combination as a new workbook using a folder path designated in cell O1.
Verify that the folder path specified in cell O1 actually exists on your computer, and ensure that your filter values do not contain characters restricted in Windows filenames (such as /, \, :, *, ?, ", <, >, |).
Use Nested AutoFilters within a VBA Macro
This automated approach loops through unique primary and secondary filter values, applying nested AutoFilters to extract data and save it into separate workbooks seamlessly.
This method involves creating two Collections in VBA. The script first gathers unique values for the primary criteria from Column E. For each unique primary value, it filters the dataset, then identifies unique secondary values from Column DF.
A nested filter is applied for both criteria. The script then uses the SpecialCells(xlCellTypeVisible) method to copy only the visible rows, pastes them into a newly created workbook, and saves it iteratively using the path defined in cell O1.
Press the ALT + F11 keys on your keyboard to launch the Visual Basic for Applications Editor in Excel.
In the Project Explorer pane on the left, right-click your workbook name, select 'Insert', and choose 'Module'.
Define your variables. Set Column E as your primary field and Column DF as your secondary field. Use nested loops to iterate through unique filter values and create a new Workbooks.Add() instance for each valid combination.
Use dataRange.SpecialCells(xlCellTypeVisible).Copy to transfer the filtered data. Save the active workbook dynamically using Workbooks.SaveAs, appending the cell O1 path with the combined primary and secondary filter names.
Return to your worksheet, press ALT + F8, select your newly created macro, and click 'Run' to begin splitting the data.
Execute VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet features robust compatibility with advanced VBA macros. You can automate complex data-splitting tasks using the exact same code and Developer tools you are already accustomed to.
- 1. Open Your Data: Launch WPS Spreadsheet and open the file containing your large dataset.
- 2. Access Developer Tools: Navigate to the 'Developer' tab located on the top ribbon.
- 3. Insert the Macro: Click 'Visual Basic Editor', insert a new Module, and build or paste your data-splitting script.
- 4. Run the Automation: Press 'Run' or hit F5 on your keyboard to execute the macro and generate the split workbooks automatically.
Frequently Asked Questions
Can I copy only values instead of copying all formulas when splitting the data?
Yes, in your macro's copy section, use a With statement on your target range and apply PasteSpecial twice: once using Paste:=xlPasteFormats and once using Paste:=xlPasteValues.
Why is the macro failing to save the newly created workbooks?
This commonly happens if the folder path in cell O1 does not exist, lacks the appropriate trailing slash, or if your primary and secondary filter values contain characters that Windows prohibits in filenames (such as colons or asterisks).
How does the macro accurately find the last row of data?
The VBA code utilizes the End(xlUp).Row method targeting the primary column. This tells Excel to jump to the very bottom of the worksheet and look upwards to locate the last non-empty cell dynamically.
