Speed Up a WPS Excel VBA Macro That Splits Data into Workbooks
Question details
The user needs to optimize a VBA macro that creates hundreds of workbooks by filtering and copying rows, because the current approach stalls or fails in Excel.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is running a VBA macro to split a large dataset into separate workbooks based on unique values in a filter column.
- Observed behavior
- Repeatedly filtering, copying, and saving workbooks causes Excel to stall or fail; screen updates and recalculation overhead slow the process.
Make sure the workbook is saved as a macro-enabled file, note the exact filter column and destination folder, and run the macro on a copy of the dataset.
Use AutoFilter and Process Each Unique Value Once
Best for large datasets because it disables screen updating, recalculates only when needed, and copies visible rows in one operation per unique value.
Repeated Copy/Paste actions and repaints make Excel freeze. The optimized routine builds a unique list, filters once per item, and writes the visible range directly.
In WPS Office Excel, press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
In the VBA editor, right-click the workbook project, select Insert > Module, and paste a macro that collects unique values, loops through them, applies AutoFilter, copies the visible range, and creates a new workbook for each filtered value.
At the top of the macro, set Application.ScreenUpdating = False, Application.EnableEvents = False, and Application.Calculation = xlCalculationManual.
Press F5 or click Run > Run Sub/UserForm, then open the target folder to confirm one workbook was created per unique value.
Include a cleanup routine with Application.ScreenUpdating = True, Application.EnableEvents = True, and Application.Calculation = xlCalculationAutomatic, placed in an error-handling block so settings are restored if a file save fails.
Use AdvancedFilter to Extract Unique Records Before Creating Workbooks
Useful when you want Excel to build the unique list for you and reduce manual dictionary handling.
Optimize Excel Splitting Macros with WPS Office
WPS Office Excel supports VBA macros, so you can run the same optimized AutoFilter splitting routine without switching tools. Use the VBA editor and macro-enabled workbook features to process large datasets more reliably in WPS Spreadsheets.
- 1. Install WPS Office: Download and install WPS Office from the official WPS site, then open the WPS Office Excel application.
- 2. Open the macro-enabled workbook: Click Menu > Open, select the .xlsm or .xlsx file that contains the splitting macro or the dataset to split.
- 3. Open the VBA editor: Press Alt+F11 or click Developer > Visual Basic to open the VBA editor and paste the optimized splitting macro.
- 4. Run the macro: Press F5 or click Run > Run Sub/UserForm to execute the macro and generate the split workbooks in your chosen folder.
- 5. Check the output files: Open the destination folder and open a generated workbook to verify that the visible-row copy and formatting are correct.
Frequently Asked Questions
Why does my macro freeze when splitting data into hundreds of workbooks?
Most freezes come from repeatedly copying ranges, redrawing the screen, and recalculating formulas. Disable ScreenUpdating, EnableEvents, and Calculation in the macro, and process each unique filter value once with AutoFilter.
Can I use this VBA splitting macro in WPS Office Excel?
Yes. WPS Office Excel includes a VBA editor and supports many Excel VBA objects and methods. Open the macro-enabled workbook, press Alt+F11, paste the routine, and run it the same way as in Excel.
How do I restore Excel settings if the macro fails during saving?
Wrap the cleanup in an error handler, for example On Error GoTo CleanUp, and in the cleanup section set ScreenUpdating and EnableEvents back to True and Calculation back to xlCalculationAutomatic before exiting.
What is the fastest way to copy only filtered rows?
Use AutoFilter and copy the visible range with SpecialCells(xlCellTypeVisible). This copies only rows matching the current filter value instead of looping row by row.
How do I handle invalid file name characters when saving unique value workbooks?
Before saving, replace characters like \ / : * ? " < > | in the filter value with an underscore or remove them using a sanitize function such as Replace().
