Use VBA to Create Progressive Dates and Rename Excel Worksheets
Question details
The user wants dates to advance across worksheets and each worksheet tab to display its corresponding date.

- 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.
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).
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.
In desktop Excel, open the workbook and select Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon.
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
Return to Excel, select Developer > Macros, choose DateAndRenameWorksheets, and select Run. Review the dates in A1 and the worksheet tabs.
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.

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.

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.




