VBA & Macro Problems

How to Clear Data from Multiple Excel Sheets with One Click

Maira Mehtab Oct 22, 2026 868 views

Question details

The user needs a method to clear specific cell ranges across all daily worksheets in a monthly Excel report simultaneously using a single click.

How to Clear Data from Multiple Excel Sheets with One Click

Product: Microsoft Excel
Device & OS: not provided
Scenario: Preparing a monthly report for a new month by erasing specific data inputs (B14:B200, N14:O200) across 31 daily worksheets without losing formulas or formatting.
Observed behavior: The user requires an automated approach to delete data across multiple sheets, specifically by assigning a VBA macro to a clickable button, while avoiding errors caused by numerical sheet names.

Before you start

Ensure the Developer tab is enabled in your spreadsheet ribbon, and remember to save a backup of your work before running any new macro, as VBA actions cannot be undone.

Solution 1Recommended

Create a VBA Macro and Assign it to a Button

Use a 'For Each' VBA script to target all worksheets regardless of their specific names, and assign it to a custom form control button for true one-click functionality.

Using a 'For Each' loop is highly recommended over a numeric index loop (e.g., 1 to 31) because it prevents errors when worksheet names are strings like '01' or if the total number of worksheets changes.

Insert the VBA Code

Open your workbook and press Alt + F11 to launch the VBA Editor. Click 'Insert' in the top menu, select 'Module', and paste the following code: Sub OneClickDelete() Dim ws As Worksheet For Each ws In Worksheets ws.Range("B14:B200,N14:O200").ClearContents Next ws End Sub

Add a Clickable Button

Return to your worksheet. Navigate to the Developer tab, click 'Insert', and choose 'Button (Form Control)'. Draw the button on your sheet where you want it located.

Assign the Macro

After drawing the button, the 'Assign Macro' dialog box will appear automatically. Select 'OneClickDelete' from the list and click 'OK'. You can now click this button to clear the data instantly.

Save as Macro-Enabled File

Go to File > Save As. In the 'Save as type' dropdown, select 'Excel Macro-Enabled Workbook (*.xlsm)' so your VBA code is preserved for future use.

Create a VBA Macro and Assign it to a Button

Preserving Formulas: Using the .ClearContents method in VBA ensures that only the data is deleted. All cell formatting, borders, colors, and formulas in other areas of the sheets remain completely intact.

Solution 2

Save the Cleared File as an Excel Template

If you prefer not to use macros, you can clear the data once manually and save the blank file as a reusable template for future months.

Efficient Data Management

Automate Your Worksheets with WPS Spreadsheet

WPS Office offers robust spreadsheet capabilities, including VBA macro support and template creation, allowing you to streamline repetitive tasks like clearing monthly report data effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the monthly report file.
2. Access the Developer Tab: Navigate to the Developer tab on the ribbon to access the Macros and Visual Basic editor.
3. Insert the VBA code: Paste your 'ClearContents' script into a new module to target all daily worksheets.
4. Add a Form Control: Insert a Form Control button directly onto your summary sheet and assign the macro for true one-click clearing.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .xltx) formats.Built-in Developer tools to run and manage custom VBA macros.Lightweight and fast performance, even with dozens of daily worksheets.Free and intuitive interface for seamless data management and automation.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro fail when my sheets are named 01 to 31?

If your macro loops using numeric sheet indexes (e.g., Worksheets(i)), it may error out if the sheets are treated as strings or if the numeric index doesn't match the exact position. Using a 'For Each ws In Worksheets' loop bypasses this issue by iterating through the sheet objects directly, completely ignoring what they are named.

Can I clear formatting along with the data contents using this macro?

Yes. If you want to remove formatting (like background colors and borders) as well as the data, change '.ClearContents' to '.Clear' in your VBA code. However, '.ClearContents' is strictly for deleting the inputted data while keeping your formatting intact.

How do I save a workbook that contains a macro button?

You must save the file in a macro-enabled format, otherwise Excel will delete the VBA code upon closing. Go to File > Save As and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the format options dropdown.

Will this macro delete data in hidden worksheets?

Yes, a standard 'For Each ws In Worksheets' loop will process hidden worksheets as well. If you have hidden reference sheets that should not be cleared, you should add an 'If ws.Visible = xlSheetVisible Then' condition inside your VBA loop.

Excel & Data Problems