How to Split Excel Workbooks with WPS Spreadsheets VBA and Include Final Rows in Every File
Question details
The user needs to split an Excel workbook into separate files based on values in a column while ensuring the final ten confirmation and signature rows appear in every output file.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A VBA macro is used to split a workbook by column values, and the final ten rows contain a confirmation statement and signature line that must be included in every generated file.
- Observed behavior
- The current macro may create only one file or overwrite it, and it does not reliably append the final ten rows to each split workbook.
Open WPS Spreadsheets, press Alt+F11 to open the VBA editor, and locate the split macro. Note the column that controls the split and the row number where the final ten rows begin so the code can reference them accurately.
Modify the VBA Macro to Append the Final Ten Rows to Each Split Workbook
Use this when the macro already splits by column but only writes one file or misses the confirmation and signature rows.
The macro should treat the final ten rows as a separate block from the main data. Export each split value to a new workbook, then copy the final block into that workbook below the exported rows before saving.
Press Alt+F11, or go to Tools > Macro > Visual Basic Editor, and open the module that contains your splitting macro.
In the macro, find the column used to group rows and set a variable that points to the start of the final ten rows, for example by locating the last used row and subtracting 9.
Change the save statement so each unique column value produces its own workbook and uses the value in the file name instead of overwriting a single output file.
After copying the grouped rows into the new workbook, copy the source range for the final ten rows and paste it below the last data row in the new workbook before the save step.
Run the macro from the VBA editor with F5 or from WPS Spreadsheets, then open several generated files to confirm each one contains the split rows and the confirmation/signature block.
Use a Template Workbook with the Final Rows Already in Place
Use this if you prefer to keep the confirmation statement and signature formatting in a ready-made template and only paste split data above it.
Split Workbooks by Column and Append Final Rows with WPS Spreadsheets
Use WPS Spreadsheets and its VBA editor to adjust the splitting macro so it creates a separate workbook for each column value and always appends your final confirmation and signature rows before saving.
- 1. Open WPS Spreadsheets and the VBA editor: Launch WPS Spreadsheets, open the workbook that contains the macro, then press Alt+F11 or go to Tools > Macro > Visual Basic Editor.
- 2. Edit the macro to create one workbook per key value: In the VBA editor, locate the subroutine that performs the split and adjust the save statement so each key value produces a new file rather than overwriting the same workbook.
- 3. Add the final rows before each save: Insert code that copies the final ten rows from the source sheet and pastes them below the exported data in the new workbook before the file is saved.
- 4. Run the macro and verify the files: Run the macro with F5, then open the generated workbooks to confirm each file contains the correct split rows followed by the confirmation statement and signature line.
- 5. Save as XLSX for sharing: Use Save As in WPS Spreadsheets and choose Excel Workbook (*.xlsx) to keep the files compatible with Microsoft Excel.
Frequently Asked Questions
How do I open the VBA editor in WPS Spreadsheets?
In WPS Spreadsheets, press Alt+F11, or click Tools > Macro > Visual Basic Editor. The editor opens in a separate window where you can view and modify the splitting macro.
Why does my macro overwrite the same workbook instead of creating a new file for each value?
The macro likely uses a fixed SaveAs path or file name. Change the save step so the file name includes the current split value and use SaveCopyAs or SaveAs to create a new workbook for each value.
How can the macro include the last ten rows without hard-coding row numbers?
Make the macro find the last used row dynamically, for example with End(xlUp), then set the final block start to that row minus 9. This keeps the final ten rows correct when the data range changes.
Does WPS Spreadsheets support Excel VBA macros for splitting workbooks?
Yes, WPS Spreadsheets includes a VBA editor and supports common Excel VBA object model calls. You can open the macro, adjust the split and save logic, and run it in WPS Spreadsheets.
What format should I save the split files in so Excel users can open them?
Save each generated workbook as Excel Workbook (*.xlsx) from the Save As dialog or use the FileFormat:=xlOpenXMLWorkbook argument in VBA to keep full Microsoft Excel compatibility.
