VBA & Macro Problems

How to Automatically Name an Excel Worksheet from a Date Using VBA

John Wilson Oct 10, 2026 869 views

Question details

The user needs to automatically set an Excel worksheet's tab name based on a date value located in a specific cell.

How to Automatically Name an Excel Worksheet from a Date

Product: Excel
Device & OS: not provided
Scenario: Attempting to use a cell containing a date (like MM/DD/YYYY) to automatically update the sheet name via a VBA macro.
Observed behavior: Standard date formats contain slash characters, which cause errors because spreadsheet software restricts the use of slashes in worksheet tab names.

Before you start

Before you begin, ensure you have enabled the Developer tab in your spreadsheet software and saved your workbook as a Macro-Enabled Workbook to allow VBA scripts to run.

Solution 1Recommended

Use a VBA Event to Format the Date and Automatically Rename the Worksheet

Apply a simple VBA script triggered by cell selection changes to reformat the date, remove unsupported slashes, and dynamically apply it to the worksheet tab name.

Since worksheet names cannot contain slashes and are limited to 31 characters, you must reformat the date into a valid text string (such as DD-MM-YYYY) before applying it.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet application.

Select the Target Worksheet

In the Project Explorer panel on the left, locate your workbook and double-click the specific worksheet where your date will be entered (for example, Sheet1).

Paste the Automation Code

Copy and paste the following code into the blank code window: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Set Target = Range("A1") If Target = "" Then Exit Sub On Error GoTo Badname ActiveSheet.Name = Format(Range("A1"), "dd-mm-yyyy") Exit Sub Badname: MsgBox "Please revise the entry in A1." Range("A1").Activate End Sub

Test the Macro

Close the VBA editor and return to your worksheet. Enter a valid date in cell A1 and press Enter. The worksheet tab name will automatically update to the formatted date.

Use a VBA Event to Format the Date and Automatically Rename the Worksheet

Character and Naming Limits: Ensure the resulting formatted date does not exceed 31 characters and does not conflict with an existing worksheet name in the same file.

Solution 2

Store Formatted Date in a VBA String Variable

If you are writing a larger macro or prefer a manual execution rather than an automatic event trigger, you can format the cell's date and store it in a string variable.

Automate Your Worksheets with Macros in WPS Office

WPS Office provides robust compatibility with Excel VBA, allowing you to seamlessly run macros, automatically rename worksheets, and streamline your daily data entry workflows.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook using the WPS Spreadsheet application.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click on the VBA Editor icon to access your macro environment.
3. Insert the VBA Code: Double-click your target worksheet in the Project window and paste your date-formatting VBA code.
4. Save as Macro-Enabled File: Save your document as an .xlsm (Macro-Enabled Workbook) to ensure your automation remains active.

Fully compatible with Microsoft Excel VBA scripts and macrosEasily format dynamic data and automate repetitive spreadsheet tasksLightweight application with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when naming a worksheet with a date?

Spreadsheet applications limit worksheet names to a maximum of 31 characters and strictly forbid certain special characters, including the forward slashes (/) or backslashes (\) that are commonly used in standard date formats.

Can I use a different date format in my VBA code?

Yes, you can modify the format string within the VBA code. For example, changing "dd-mm-yyyy" to "yyyy-mm-dd" or "mmm-dd-yyyy" will work perfectly, provided you do not introduce forbidden characters like slashes, asterisks, or brackets.

Will the tab name update automatically if I change the date in the cell?

The tab name will only update automatically if your VBA script uses a Worksheet Event, such as Worksheet_Change or Worksheet_SelectionChange, which continuously monitors the target cell for new inputs.

Excel & Data Problems