VBA & Macro Problems

Use VBA to Create Progressive Dates and Rename Excel Worksheets

Adam DavisAdam Davis Nov 22, 2026 868 views

Question details

The user wants dates to advance across worksheets and each worksheet tab to display its corresponding date.

Create Progressive Dates and Rename Excel Worksheets with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook contains worksheets representing successive academic or calendar dates, starting from a date on the first worksheet.
Observed behavior
Each worksheet should contain the next date in the sequence, and its tab name should match that date.
Before you start

Place a valid starting date in cell A1 of the first worksheet and arrange the worksheets in the desired order. The macro overwrites A1 on every worksheet and renames tabs; save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Advance dates and rename worksheet tabs with VBA

Use this macro when each worksheet should advance by one day and display its date as YYYY-MM-DD.

Worksheet formulas can calculate dates, but they cannot rename tabs. This macro writes date values rather than formulas, so run it again if the starting date or worksheet order changes.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon.

2
Insert the macro

In the VBA editor, select the workbook in Project Explorer, choose Insert > Module, and paste this code: Sub DateAndRenameWorksheets() Dim ws As Worksheet Dim existing As Object Dim startDate As Date Dim currentDate As Date Dim targetName As String Dim i As Long If Not IsDate(ThisWorkbook.Worksheets(1).Range("A1").Value) Then MsgBox "Enter a valid starting date in A1 of the first worksheet." Exit Sub End If If ThisWorkbook.ProtectStructure Then MsgBox "Unprotect the workbook structure before running the macro." Exit Sub End If startDate = CDate(ThisWorkbook.Worksheets(1).Range("A1").Value) startDate = DateSerial(Year(startDate), Month(startDate), Day(startDate)) On Error GoTo StopMacro For i = 1 To ThisWorkbook.Worksheets.Count Set ws = ThisWorkbook.Worksheets(i) currentDate = DateAdd("d", i - 1, startDate) targetName = Format$(currentDate, "yyyy-mm-dd") For Each existing In ThisWorkbook.Sheets If LCase$(existing.Name) = LCase$(targetName) Then If Not (existing Is ws) Then MsgBox "The tab name " & targetName & " is already in use. Rename that tab and run the macro again." Exit Sub End If End If Next existing ws.Range("A1").Value = currentDate ws.Range("A1").NumberFormat = "yyyy-mm-dd" ws.Name = targetName Next i MsgBox "Worksheet dates and names updated." Exit Sub StopMacro: MsgBox "The macro stopped. Check sheet protection, dates, and tab names, then try again." End Sub

3
Run the macro

Return to Excel, select Developer > Macros, choose DateAndRenameWorksheets, and select Run. Review the dates in A1 and the worksheet tabs.

4
Adjust the interval if needed

In the VBA editor, change DateAdd("d", i - 1, startDate) to DateAdd("d", (i - 1) * 7, startDate) for weekly dates, then run the macro again.

Advance dates and rename worksheet tabs with VBA
Check naming conflicts: The macro stops if a target tab name is already in use, including by a chart sheet. Earlier worksheets may already have been changed; rename the conflicting tab before running it again. Protected worksheet cells can also prevent the macro from updating A1.
Free Microsoft Office alternative

Manage dated worksheets with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files, work with date formulas, and rename tabs manually as you move your spreadsheet workflow to WPS. VBA macro availability and compatibility can vary by version and installation.

Use date formulas to build sequences across worksheets.Open and edit Microsoft Excel XLSX files in WPS Spreadsheets.Navigate worksheets with a familiar tab-based interface.Use a lightweight office suite with a free version.
microsoft office alternative - wps office

Frequently Asked Questions

Can an Excel formula rename a worksheet tab?

No. A formula can calculate a date in a cell, but changing a tab name requires a manual rename or automation such as VBA.

How do I make each date advance by a week instead of a day?

In the macro, replace DateAdd("d", i - 1, startDate) with DateAdd("d", (i - 1) * 7, startDate), then run it from Developer > Macros.

Why did the macro stop at a worksheet?

A target tab name may already exist, the workbook structure may be protected, or a worksheet may prevent changes to A1. Check the message, resolve the issue, and run the macro again. Earlier sheets may already have been updated.

Will the tab names update automatically when I change A1?

No. This macro runs on demand. After changing the starting date in A1 of the first worksheet, select Developer > Macros and run DateAndRenameWorksheets again.

How can I calculate the next date without VBA?

On the next worksheet, enter a formula such as ='Sheet1'!$A$1+1 in A1, replacing Sheet1 with the previous worksheet's name. Rename the tab manually to match the resulting date.