Create Daily Excel Worksheets for Every Date in a Month
Question details
Create a separate Excel worksheet for every date in a selected month and name each sheet for its day.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants 28 to 31 daily worksheets for a month, optionally using a consistent template.
- Observed behavior
- Each date in the selected month should have its own worksheet with a name such as 5 Mar.
Use a desktop version of Excel that supports VBA, and check that the workbook structure is not protected. Create a worksheet named BlankMonthSheet with the formatting or content you want on each daily sheet.
Use a VBA macro to copy and name daily worksheets
Create one copy of a template for each date in the month, automatically accounting for different month lengths and leap years.
The macro names sheets in the d mmm format and skips names that already exist. Because the names do not include a year, a sheet such as 5 Mar from another year will also be skipped.
In desktop Excel, open the workbook containing BlankMonthSheet. Open the Visual Basic editor from Developer > Visual Basic; if the Developer tab is hidden, enable it in Excel's ribbon settings.
In the VBA editor, select the workbook, choose Insert > Module, and paste this code: Sub CreateDailySheets() Dim entered As String Dim firstDay As Date Dim dayCount As Long Dim i As Long Dim sheetName As String Dim template As Worksheet Dim existing As Worksheet entered = InputBox("Enter a date in the month to create:", "Create monthly sheets") If Trim(entered) = "" Then Exit Sub If Not IsDate(entered) Then MsgBox "Please enter a valid date.", vbExclamation Exit Sub End If On Error Resume Next Set template = ThisWorkbook.Worksheets("BlankMonthSheet") On Error GoTo 0 If template Is Nothing Then MsgBox "Create a worksheet named BlankMonthSheet first.", vbExclamation Exit Sub End If firstDay = DateSerial(Year(CDate(entered)), Month(CDate(entered)), 1) dayCount = Day(DateSerial(Year(firstDay), Month(firstDay) + 1, 0)) For i = 1 To dayCount sheetName = Format(firstDay + i - 1, "d mmm") Set existing = Nothing On Error Resume Next Set existing = ThisWorkbook.Worksheets(sheetName) On Error GoTo 0 If existing Is Nothing Then template.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) ActiveSheet.Name = sheetName End If Next i MsgBox "Daily sheets created. Existing sheet names were skipped." End Sub
In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Then choose Developer > Macros, select CreateDailySheets, and click Run.
In the prompt, enter any valid date in the desired month using a format Excel recognizes, then click OK. Check the worksheet tabs for the dates; existing names are skipped.

Build a Month of Daily Worksheets in WPS Spreadsheets
Use WPS Spreadsheets to create and name daily tabs manually, or copy a prepared template when you need consistent formatting.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to load your workbook, or create a new spreadsheet.
- 2. Prepare a template: Set up a worksheet with the formatting and headings you want on each daily sheet.
- 3. Create and name daily sheets: Use the plus button beside the sheet tabs to add a sheet, or right-click the template tab and use the copy option if available. Double-click each new tab and enter its date, such as 5 Mar.
- 4. Repeat and save: Repeat for every date in the month, then choose File > Save As and select XLSX to save the workbook.

Frequently Asked Questions
How does the macro handle February and leap years?
The macro uses VBA's DateSerial function to find the last day of the selected month, so it creates 28 or 29 sheets for February as appropriate.
What happens if a daily sheet already exists?
The macro skips any sheet name already in the workbook. Check the worksheet tabs before running it for another year, since names such as 5 Mar do not include the year.
Why does the macro require BlankMonthSheet?
The macro copies BlankMonthSheet for each date so that every daily sheet starts with the same formatting and content. Create or rename a worksheet to BlankMonthSheet before running it.
Can I run this macro in Excel for the web?
No. Excel for the web cannot run VBA macros. Open the workbook in desktop Excel and use Developer > Macros to run CreateDailySheets.




