VBA & Macro Problems

How to Apply a VBA Macro to Selected and Future Excel Worksheets

Rana Garcia Oct 14, 2026 869 views

Question details

The user needs to apply formatting to all visible Excel worksheets except for specific named sheets, and wants this formatting macro to run automatically when new worksheets are created.

How to Apply a VBA Macro to Selected and Future Excel Worksheets

Product: Excel
Device & OS: not provided
Scenario: Automating worksheet formatting across multiple existing visible tabs and newly generated tabs using VBA.
Observed behavior: A VBA solution is required to loop through sheets, filter by visibility and name, and hook into workbook events to handle future worksheet creation.

Before you start

Ensure you have enabled the Developer tab in your ribbon to access the VBA editor, and remember to save your file as an Excel Macro-Enabled Workbook (.xlsm) when finished.

Solution 1Recommended

Create a VBA Macro Loop and Configure the Workbook_NewSheet Event

Use a VBA loop to process visible worksheets while excluding specific names, and utilize the Workbook_NewSheet event to automate the process for future sheets.

This solution involves writing a standard module macro to loop through existing sheets, and then calling that same macro from a workbook-level event so it applies to any new sheets you create later.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. From the top menu, click 'Insert' and select 'Module'.

Paste the Macro Code

In the new module, create your macro (e.g., Sub Apply2Sheets). Use a 'For Each wsh In Worksheets' loop. Inside the loop, check if the sheet is visible using 'If wsh.Visible = xlSheetVisible'. Use a 'Select Case LCase(wsh.Name)' statement to exclude sheets like 'raw data', 'workslate', and 'pickup'. For all other sheets, apply your formatting to the specific range (e.g., AQ6:AQ36).

Access ThisWorkbook Events

In the Project Explorer panel on the left side of the VBA editor, locate your workbook project and double-click on 'ThisWorkbook' to open its code window.

Automate for Future Sheets

In the 'ThisWorkbook' code window, select 'Workbook' from the top-left dropdown menu, and 'NewSheet' from the top-right dropdown menu. Inside the 'Private Sub Workbook_NewSheet' block, type 'Call Apply2Sheets' (or whatever you named your macro) to ensure the macro runs every time a new sheet is added.

Create a VBA Macro Loop and Configure the Workbook_NewSheet Event

VBA Execution Safety: Macros cannot be undone using the standard Undo (Ctrl+Z) function. Always save a backup copy of your workbook before running new VBA scripts.

Advanced Spreadsheet Automation

Automate Worksheets with Macros in WPS Spreadsheets

WPS Spreadsheets provides excellent support for VBA macros, allowing you to easily automate repetitive tasks across multiple worksheets just like you would in Excel.

1. Open your macro workbook: Launch WPS Spreadsheets and open your existing .xlsm macro-enabled file.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon and click on the 'VBA Editor' icon.
3. Add or Edit VBA Code: Use the Project Explorer to navigate modules and ThisWorkbook, then paste or edit your VBA macro loops and events.
4. Run and Automate: Run your macro directly from the editor or the Developer tab to format your visible and future worksheets.

Full compatibility with Microsoft Excel (.xlsm) macro-enabled filesRobust built-in VBA editor for writing and executing custom macrosLightweight software with fast execution of complex scriptsSeamless transition with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

How do I exclude additional worksheet names from the macro loop?

You can easily exclude more sheets by adding their names in lowercase to the 'Case' statement within your VBA code, separating each new name with a comma (e.g., Case "raw data", "workslate", "pickup", "summary").

Why isn't my Workbook_NewSheet event triggering automatically?

Ensure that macros are enabled in your security settings (Macro Security -> Enable All Macros) and verify that the event code is placed specifically in the 'ThisWorkbook' object module, not in a standard module.

Can I undo a VBA macro execution if I made a mistake?

No, actions performed by a VBA macro usually clear the undo history and cannot be undone using the standard Undo button. It is highly recommended to save a backup of your workbook before running new macros.

Excel & Data Problems