Home > Structure > Workbook > Add Worksheets Create Excel Workbooks Worksheets AutomaticallyCreate 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 AutomaticallyTo 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 OpensIn 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 OpensSomeone 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 MacroBefore 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 GoalFor 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 StepsHere 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 MonthFirst, 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: Format(Date, "yyyy_mm") Other Date FormatsInstead 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
Format(Date, "mm_mmm") Variable for Date Text StringIn 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 SheetNext, 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 FalseIf 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 NeededThe 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 WorksheetHere 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 AutomaticallyTo 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 YearIn 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 MacroAfter 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 MonthWithin each workbook, the macro will create a worksheet for each day of the month.
Two Versions of Create Workbooks MacroThere are two versions of the Create Workbooks Macro workbook:
Both versions are in the Download section below. |
Download Sample Files
|
More Tutorials |
Last updated: November 13, 2023 11:25 AM