VBA & Macro Problems

Create Daily Excel Worksheets for Every Date in a Month

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Create a separate Excel worksheet for every date in a selected month and name each sheet for its day.

Create Daily Excel Worksheets for Every Date in a Month
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

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.

2
Insert the macro

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

3
Save and run the macro

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Then choose Developer > Macros, select CreateDailySheets, and click Run.

4
Enter a date and check the tabs

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.

Use a VBA macro to copy and name daily worksheets
Macro availability: Excel for the web cannot run VBA macros. Open the workbook in desktop Excel and enable macros only if you trust the workbook and its code.
Create daily sheets in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to load your workbook, or create a new spreadsheet.
  2. 2. Prepare a template: Set up a worksheet with the formatting and headings you want on each daily sheet.
  3. 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. 4. Repeat and save: Repeat for every date in the month, then choose File > Save As and select XLSX to save the workbook.
Create a named worksheet for each date in the monthReuse a template's layout and formatting across daily sheetsOpen and save Microsoft Excel XLSX workbooksWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.