VBA & Macro Problems

How to Create Daily Excel Worksheets for a Fiscal Year using VBA

Chanuka Geekiyanage Oct 23, 2026 870 views

Question details

The user needs to automatically generate a separate worksheet for every day of a fiscal year (e.g., October 1 to September 30) using an Excel VBA macro.

How to Create Daily Excel Worksheets for a Fiscal Year using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up a fiscal year tracking workbook that requires daily sheets formatted chronologically with specific date names.
Observed behavior: Instead of manually creating and renaming 365 tabs, the user wants an automated script to generate sheets named in a specific date format, such as "Mmm dd, yyyy (Ddd)".

Before you start

Ensure you have saved any existing work before running the macro, and verify that your spreadsheet software supports VBA modules. You will need to save the final file in a macro-enabled format (.xlsm) to preserve the script.

Solution 1Recommended

Run a VBA Macro to Generate Daily Sheets

Use the built-in VBA editor to execute a script that loops through the dates of the fiscal year, creates a sheet for each day, and names them accordingly.

This method leverages Visual Basic for Applications (VBA) to automate the tedious task of creating 365 or 366 daily worksheets. The script handles the formatting of the sheet names and organizes them chronologically from the start date to the end date.

Open the VBA Editor

Open a new or existing workbook in Excel and press Alt + F11 to launch the Visual Basic for Applications Editor.

Insert a New Module

In the top menu, go to Insert > Module to create a blank standard module where you can write or paste your macro code.

Paste and Run the Macro

Paste your daily sheet generation code (e.g., a DoDays script defining your specific fiscal year dates). Press F5 or click the Run button to execute the macro. Wait a few moments as the sheets are automatically generated.

Save as Macro-Enabled

Once the sheets are created, go to File > Save As and select "Excel Macro-Enabled Workbook (*.xlsm)" to safely save your workbook and its new macro.

Run a VBA Macro to Generate Daily Sheets

Clean Up Extra Sheets: A well-written macro can automatically delete the temporary starting sheet (e.g., 'Sheet1') after all the daily sheets have been generated, keeping your workbook perfectly clean.

Solution 2

Manually Organize and Adjust Worksheet Tabs

How to organize or refine your generated sheets if you need to reposition tabs or apply global formatting.

Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet offers powerful support for VBA and macros, allowing you to easily run scripts to automate daily worksheet creation just like in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and create a new blank workbook in WPS Spreadsheet.
2. Access the VBA Editor: Navigate to the Tools tab on the ribbon and click on Macro > Visual Basic Editor, or simply press Alt + F11.
3. Run the Script: Insert a new module, paste your daily sheet creation VBA code for the fiscal year, and click Run.
4. Save the Workbook: Save your newly generated fiscal year workbook as a .xlsm file to retain full functionality.

Fully compatible with Microsoft Excel macro-enabled formats (.xlsm)Advanced VBA editor support for writing, debugging, and running automation scriptsLightweight design ensures fast processing even when creating 365 sheets at onceCost-effective alternative for heavy data automation tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why are my worksheet names giving an error when running the macro?

Worksheet names cannot contain certain characters like colons, backslashes, square brackets, or question marks, and cannot exceed 31 characters. Ensure your VBA date format (e.g., "Mmm dd, yyyy (Ddd)") complies with these limits.

Can I modify the macro for a different fiscal or calendar year?

Yes. You can easily adjust the start date and end date variables in your VBA script's loop. For example, changing the start date to "Jan 1, 2024" and the end date to "Dec 31, 2024" will generate sheets for a standard calendar year.

How do I remove the macro after generating the daily sheets?

If you only needed the macro to generate the sheets and no longer need the script, save the workbook as a standard Excel Workbook (.xlsx). This will strip the VBA code from the file automatically while keeping all your newly created daily tabs.

Excel & Data Problems