VBA & Macro Problems

How to Restore an Excel Macro That Renames Worksheets with a Date

Natalie Taylor Sep 28, 2026 870 views

Question details

The user needs to recreate a missing VBA macro that automatically renames specific worksheets using a date value found in cell H1.

How to Restore an Excel Macro That Renames Worksheets with a Date

Product: Microsoft Excel
Device & OS: not provided
Scenario: The user upgraded to Microsoft 365 and lost their original VBA macro used for renaming sheets like "AR - AS OF [date]" and "AP - AS OF [date]".
Observed behavior: The original macro is missing or no longer functions post-upgrade, requiring a recreation of the VBA script using exact worksheet names.

Before you start

Before creating or editing macros, ensure that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your ribbon.

Solution 1Recommended

Recreate the Sheet Renaming VBA Macro

Since the original macro is missing, writing a new VBA script is the most direct way to restore the functionality of renaming sheets based on cell H1.

When rewriting the macro, you must ensure that the date format in cell H1 is compatible with Excel's naming rules. Worksheets cannot contain slashes or asterisks.

Open the VBA Editor

Launch your workbook in Excel and press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the Project Explorer pane on the left, right-click on your workbook's name, select 'Insert', and then click 'Module'.

Write the VBA Code

Paste a VBA script that defines the new sheet names using the value from cell H1. For example: Sheets("AR").Name = "AR - AS OF " & Format(Range("H1").Value, "mm-dd-yyyy").

Save as a Macro-Enabled Workbook

Close the VBA Editor. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown to ensure your new macro is saved.

Date Format Warning: Ensure the date format specified in your VBA code uses dashes (-) instead of slashes (/). Slashes are illegal characters in Excel worksheet names and will cause a runtime error.

Solution 2

Recover the Original Macro from a Previous Backup

If you recently upgraded to Microsoft 365, the original macro-enabled file might still exist on your local drive or in your cloud storage version history.

Easily Manage and Run VBA Macros in WPS Office

WPS Spreadsheet provides excellent support for VBA macros, allowing you to seamlessly run your sheet-renaming scripts and automate daily tasks just like you would in Microsoft Excel.

1. Install WPS Office: Download and install WPS Office, ensuring your edition includes VBA module support.
2. Open the Workbook: Launch WPS Spreadsheet and open your existing macro-enabled workbook (.xlsm).
3. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click 'Macros' or 'Visual Basic' to access your renaming scripts.
4. Run or Edit the Macro: Select your worksheet-renaming macro from the list and click 'Run', or edit the code directly within the WPS VBA Editor.

Fully compatible with Microsoft Excel (.xlsm) macro-enabled filesFamiliar Developer tab interface for writing and editing VBA scriptsLightweight application with fast macro execution and data processingFree and intuitive alternative for managing complex spreadsheets

microsoft office alternative - wps office

Frequently Asked Questions

Why did my macro disappear after upgrading to Microsoft 365?

Upgrading to a new Office version might change your default save formats. If you accidentally saved the file as a standard Excel Workbook (.xlsx) instead of a Macro-Enabled Workbook (.xlsm), all VBA code is automatically stripped out and removed.

What characters are forbidden when using a date to rename an Excel sheet?

Excel worksheet names cannot exceed 31 characters and cannot contain the following characters: \, /, ?, *, [, or ]. When pulling a date from cell H1 via VBA, ensure you format it with dashes (e.g., MM-DD-YYYY) instead of slashes.

How do I automatically run the renaming macro when the date in cell H1 changes?

You can use a Worksheet_Change event in the VBA Editor. Right-click the sheet tab, select 'View Code', and write a script that triggers your renaming macro whenever the value in Target.Address matches "$H$1".

Excel & Data Problems