VBA & Macro Problems

How to Run an Excel VBA Macro on Every Worksheet Except the First

Nimra Malik Oct 8, 2026 869 views

Question details

The user needs a VBA macro that iterates through all worksheets in a workbook to clear specific constant values, excluding the very first worksheet.

How to Run an Excel VBA Macro on Every Worksheet Except the First

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data cleanup across multiple sheets while preserving the master/first sheet and keeping any formulas in the targeted ranges intact.
Observed behavior: The goal is to successfully loop through worksheets using their index and use SpecialCells to clear contents without triggering runtime errors if no constants are found.

Before you start

Make sure to save a backup copy of your workbook before running any macro, as VBA actions like clearing cell contents usually cannot be undone.

Solution 1Recommended

Use a VBA Loop with Index Checking to Exclude the First Sheet

This method iterates through the workbook's sheets and executes your code only when the sheet's index number is greater than 1.

By combining a For Each loop with an If statement checking the sheet index, you can safely skip the first sheet. Using SpecialCells(xlCellTypeConstants) ensures that only hardcoded values are cleared while formulas are kept intact. Adding On Error Resume Next prevents runtime errors if a sheet contains no constants to clear.

Open the VBA Editor

Press Alt + F11 to open the VBA Editor in your spreadsheet program.

Insert a New Module

Navigate to the top menu and click Insert > Module to create a blank workspace for your macro.

Paste the Macro Code

Copy and paste the following VBA code into the module window: Sub ClearTimes() Dim ws As Worksheet On Error Resume Next For Each ws In ThisWorkbook.Worksheets If ws.Index > 1 Then ws.Range("B3:Q16").SpecialCells(xlCellTypeConstants, 23).ClearContents End If Next ws End Sub

Run the Macro

Modify the range "B3:Q16" to match your specific requirements, then press F5 or click the Run button to execute the macro across the workbook.

Use a VBA Loop with Index Checking to Exclude the First Sheet

Understanding Error Handling: The 'On Error Resume Next' line is crucial in this script. The SpecialCells method throws an error if it finds no matching cells (e.g., a sheet with only formulas or blank cells in the range). This line forces VBA to ignore the error and proceed to the next sheet safely.

Automate Tasks with WPS Spreadsheet

Seamlessly Run Macros and Automate Tasks in WPS Office

WPS Spreadsheet provides robust support for VBA and macros, allowing you to easily loop through worksheets, manipulate data, and automate repetitive tasks just like you would in Microsoft Excel.

1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm or .xlsx workbook that requires automation.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on the 'Visual Basic' icon, or simply press Alt + F11.
3. Execute Your Script: Insert a new Module, paste your sheet-looping macro code, and run it to instantly clean up your specific data ranges.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) macro formats.Built-in robust support for VBA scripting and macro execution.Lightweight, lightning-fast, and runs smoothly on Windows, Mac, and Linux.Cost-effective Office alternative with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How can I exclude multiple specific worksheets by name instead of by index?

You can use a Select Case statement or an If statement checking the 'ws.Name' property instead of 'ws.Index'. For example, you can write: 'If ws.Name <> "Master" And ws.Name <> "Summary" Then' to skip those specific sheets.

Why does the SpecialCells method throw an error in Excel VBA?

If the SpecialCells method does not find any cells matching the criteria you specified (like constants in a targeted range), it triggers a runtime error. Using 'On Error Resume Next' immediately before the line tells VBA to skip the error and move on.

Can I modify this macro to clear both constants and formulas?

Yes. Instead of using 'ws.Range("B3:Q16").SpecialCells(xlCellTypeConstants, 23).ClearContents', you can simply use 'ws.Range("B3:Q16").ClearContents'. This will wipe out all data, including formulas and values, within the specified range on the targeted sheets.

How do I run this macro on every single worksheet, including the first one?

To apply the clearing action to every worksheet, simply remove the 'If ws.Index > 1 Then' line and its corresponding 'End If' line from the VBA code. The loop will then process all sheets in the workbook without skipping.

Excel & Data Problems