Combine Multiple Excel Worksheets into a Master Sheet via VBA
Question details
The user needs an automated way to merge approximately 50 worksheets into a single master sheet, separate each copied section with a printed page break, and delete the original source sheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating a large number of dynamically generated worksheets into one master report for printing, where each original sheet starts on a new page.
- Observed behavior
- Doing this manually is too time-consuming because the source worksheets are generated by another process and change over time, requiring a fast, automated VBA solution.
Always create a backup copy of your workbook before running this macro, as deleting worksheets via VBA is permanent and cannot be undone using the standard undo shortcut.
Use a VBA Macro to Combine Sheets and Insert Page Breaks
A complete VBA script that loops through all sheets, copies data to a Master sheet, adds a horizontal page break, and deletes the source sheets safely.
This macro will create a new 'Master' worksheet, copy the used range of every other worksheet into it sequentially, insert horizontal page breaks after each section, and permanently delete the source worksheets.
To prevent certain worksheets from being deleted, you will need to add an exclusion condition in your VBA code.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications Editor in Excel.
Click 'Insert' from the top menu, then select 'Module' to create a blank canvas for your script.
Paste your macro code into the module window. Ensure you add logic like 'If QSh.Name <> "Master" And QSh.Name <> "Sheetkeep1" Then' to exclude specific sheets from being modified or deleted.
Before running the macro, if your source sheets use conditional formatting, make sure the formulas are relative (e.g., =COUNTIF(F4:K4,"Yes")) so they adapt properly when pasted.
Press F5 or click the 'Run' button (the green play icon) to execute the macro. Check your new Master sheet to verify the data and page breaks.
Combine Worksheets Using Macros in WPS Spreadsheet
WPS Spreadsheet offers excellent support for VBA and macros, allowing you to automate repetitive tasks like combining dozens of worksheets into a master sheet just as seamlessly as Microsoft Excel.
- 1. Open the Macro Settings: Open your workbook in WPS Spreadsheet, navigate to the 'Tools' tab, and click on 'Developer' to access the Macro tools.
- 2. Launch the VBA Editor: Click on 'Visual Basic Editor' (or press Alt + F11) to open the scripting window.
- 3. Insert and Run Your Macro: Click 'Insert' > 'Module', paste your consolidation macro code, and click 'Run' to combine your worksheets instantly.
Frequently Asked Questions
How can I keep certain worksheets from being deleted by the macro?
You can exclude specific sheets by adding conditions to your VBA loop. For example, use 'If ws.Name <> "Master" And ws.Name <> "DataSheet" Then' to ensure those sheets are skipped during the copy and delete process.
Can I undo a macro after it deletes my worksheets?
No, actions performed by a VBA macro—especially deleting worksheets—cannot be reversed using the standard Undo feature. Always run consolidation macros on a backup copy of your file first.
Why is my macro copying conditional formatting incorrectly?
If your source sheets contain conditional formatting, ensure the formulas are relative (e.g., =COUNTIF(F4:K4,"Yes") instead of using absolute references like $F$4:$K$4) before running the macro, so they adjust correctly when pasted into the new Master sheet rows.
Does WPS Office support running this Excel VBA macro?
Yes, WPS Office provides robust support for Excel VBA macros. You can run, edit, and create macros directly within the Developer tab in WPS Spreadsheet just as you would in Microsoft Excel.
