Home > Structure > Workbook > Add Worksheets

Create Excel Workbooks Worksheets Automatically

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:

• Automatically add new worksheet at start of month
• Click button to create monthly workbooks, with daily sheets, for upcoming year

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:

• figure out what the current month is
• check for a sheet for that month
• add that month sheet, if it doesn't exist already

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:

• Date - returns the current date
• Format - formats a number or date in a specific format

In this example, the worksheet names will have this format: yyyy_mm

• yyyy: 4-digit year number
• _: underscore
• mm: two-digit month number

Here is the VBA code to return the current date, as a text string, in yyyy_mm format:

Format(Date, "yyyy_mm")

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

• mmmm - full month name, e.g. January
• mmm - 3-digit month name, e.g. Jan
• mm - 2-digit month number, e.g. 01
• mm_mmm - month number and name, e.g. 00_Jan

Format(Date, "mm_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)

Sheets(strName).Name

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:

• That sheet's name will have at least one character (greater than 0)
• bCheck = True

If the workbook does NOT a sheet with the current month's name:

• That sheet does not exist, so there isn't a sheet name to check for length
• The nonexistent name does not have any characters (NOT greater than 0)
• bCheck = False

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

• This section of the macro only runs if the bCheck variable is False (sheet not in workbook).
• First, the code adds a new worksheet in the active workbook, to the left of the first sheet that's currently in the workbook.
• Then, the macro names that sheet, using the formatted date 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:

• Open the Visual Basic Editor
• In the Project Explorer list, find your workbook
• Right-click on the ThisWorkbook module, in the Microsoft Excel Objects folder.
• In the pop-up menu, click on View Code
• Then, paste the code below into the Code Module window that appears at the right side of the screen

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.

• This macro will create a series of 12 workbooks in the same folder as this workbook is stored.
  • Tip: Store the macro workbook in a new folder, named for the upcoming Year
• Each new workbook will be named with 3-character month name, and year e.g. Jan 2023 through Dec 2023.
• After you click the Macro button, you’ll be prompted to enter the year number at the beginning of the macro.

• Another message asks if you want the month's day to be in ordinal format on the sheet tabs, like 1st, 2nd, etc.
  • Click Yes if you want Ordinals
  • Click No for normal number format

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:

• Right-click on the file, and click Properties
• If there is a Security message at the bottom, click the Unblock setting, to remove the check mark
• Click OK to close the Properties window.

Next, open the sample file, and go to the Information worksheet. There's a notes section there, so start by reading that.

• After you read the macro notes, click the worksheet button, to start the macro.

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.

• There’s an option to display the numbers as ordinals, so the sheet names would be Jan 1st, Jan 2nd and so on.

Two Versions of Create Workbooks Macro

There are two versions of the Create Workbooks Macro workbook:

1. xls Format: Roger's original file creates workbooks in xls format - compatible with any version of Excel
2. xlsx Format: My updated version creates workbooks in xlsx format - for Excel 2007 or later

Both versions are in the Download section below.

Download Sample Files

• Add Sheet When File Opens -- Automatically add a new worksheet with the month name, when the Excel file opens at the start of the month. ExcelVBA_AddMonthSheet.zip 
• Monthly Workbook Creator (xls) -- Click a button, and the code in this file creates a workbook (xls format) for each month of the year, with a sheet for each day. Excel template from Roger Govier. CreateMthlyWkbks.zip
• Monthly Workbook Creator (xlsx) -- Click a button, and macro creates a workbook (xlsx format) for each month of the year, with a sheet for each day. Updated version of original template. excelcreateyrmthworkbks.zip

More Tutorials

Workbook Tips

Workbooks, List and Close

Worksheet Macros

Macros - Get Started

Macros - Save Sheets As PDF