Create worksheets automatically when file opens at start of month - watch the macro to see the steps. In another example, click a worksheet button to create monthly workbooks and worksheets for entire year.
Create Worksheets Automatically
To make the most of Excel data analysis features, like pivot table and filters, it's best to have your data in one main table. However, sometimes you need to work with your data in a less than ideal setup, such as separate worksheets for each month, or in a different file for each month, with daily worksheet.
The macros on this page will help you set those up quickly, instead of manually creating the worksheets and workbooks. In the sections below there are steps to:
At the end of the page, you can download the sample Excel files with the macro VBA code that you need for these tasks.
Add Sheet When Excel File Opens
In this video, see how to automatically add a new worksheet with the month name, when an Excel file opens at the start of the month.
There are written steps below the video, and you can download the sample file at the end of the page.
Macro to Add Sheet When Workbook Opens
Someone asked me how to automatically add a new month sheet, when they opened their Excel file at the betinning of the month. So, I created this macro and sample file, and the video that shows how the macro works automatically.
Planning the Macro
Before writing an Excel macro, make a plan for what the macro needs to do.
-- 1) Overall Goal
-- 2) Required Steps
The plans for the worksheet macro are shown below
1) Overall Goal
For this macro, the goal is to add a new, blank worksheet with the month name, when the Excel file opens. This should only happen at the start of the month, if the current month's sheet doesn’t exist already.
2) Required Steps
Here are the basic steps that this macro needs to do:
In the next section, you'll see how to do those steps with Excel VBA code in a macro.
Step 1: Get Current Month
First, to get the month name in Excel VBA code, you can combine two VBA functions:
In this example, the worksheet names will have this format: yyyy_mm
Here is the VBA code to return the current date, as a text string, in yyyy_mm format:
Other Date Formats
Instead of using the yyyy_mm date format, you could use one of the following formats in the VBA code, if you prefer.
NOTE: If there are multiple years of data in the workbook, be sure to include the year number in the date string, e.g. yyyy_mmm
Variable for Date Text String
In the macro, there is a variable named strName. It stores the results from the Format and Date formula, as a text string.
strName = Format(Date, "yyyy_mm")
Step 2: Check for Existing Sheet
Next, the Excel VBA code will check for an existing sheet with the current month name, in the format that your macro uses. For this step, the code uses the strName variable that stores the date text string.
First, the code tries to get the name of the sheet named with the date text string (strName)
Next, the code uses the VBA Len function to check the length of that sheet name, and see if it is greater than zero characters - True or False
Len(Sheets(strName).Name) > 0
In the macro, there is a variable named bCheck. It stores the results from the length test formula, as a Boolean value (true or false)
bCheck = Len(Sheets(strName).Name) > 0
True or False
If the workbook already has a sheet with the current month's name:
If the workbook does NOT a sheet with the current month's name:
Step 3: Add Sheet if Needed
The final step in the macro is to add a new worksheet, if needed, using the date text string
If bCheck = False Then Set ws = Worksheets.Add(Before:=Sheets(1)) ws.Name = strName End If
Full Macro to Add Month Worksheet
Here is the completed macro code to add a new worksheet, if needed, using a formatted date string as its name. This macro is stored on a regular code module in the workbook
Sub AddMonthWkst() Dim ws As Worksheet Dim strName As String Dim bCheck As Boolean On Error Resume Next strName = Format(Date, "yyyy_mm") bCheck = Len(Sheets(strName).Name) > 0 If bCheck = False Then Set ws = Worksheets.Add(Before:=Sheets(1)) ws.Name = strName End If End Sub
Run Macro Automatically
To make the AddMonthWkst macro run automatically, when the workbook opens, you’ll create a Workbook_Open event. Instead of storing this event code in a regular code module, you'll store it in the Workbook module.
To add this code to your own workbook:
Here is the event code to copy and paste:
Private Sub Workbook_Open() 'add new sheet each month AddMonthWkst End Sub
Create Workbooks for Year
In the download section below, there is another sample Excel file that automatically creates worksheets, and it creates workbooks too, created by Roger Govier.
You can use this macro at the end of the year, when you want to set up your files for the upcoming year.
Run the Macro
After you download the Create Workbook macro file, go to Windows Explorer, find the downloaded file, and follow these steps to unblock it:
Next, open the sample file, and go to the Information worksheet. There's a notes section there, so start by reading that.
Remember -- the monthly workbooks will all be created in the same folder where the Create Workbooks macro file is stores.
Worksheet for Each Day in Month
Within each workbook, the macro will create a worksheet for each day of the month.
Two Versions of Create Workbooks Macro
There are two versions of the Create Workbooks Macro workbook:
Both versions are in the Download section below.
Download Sample Files
Last updated: November 29, 2022 2:13 PM