On the Contextures YouTube channel, someone asked if Excel can automatically create a sheet when the file opens:
- “this question from my friend, she always need to open same file and create a new sheet with the name of the month when her is updating the data. is there any ways to skip the above process?”
Video Answer
Since the question came from YouTube, I provided the answer in a video, which you can see at the end of this blog post.
If you prefer to see the code, or download a sample file, you can find the details below.
How the Add Sheet Code Works
The goal is to add a worksheet with the month name, when the Excel file opens. We only want that to happen at the start of the month, if the sheet doesn’t exist already.
We’ll write a macro that:
- figures out what the current month is
- checks for a sheet with that name
- adds that sheet, if it doesn’t exist
The Add Worksheet Code
Insert a regular module in the workbook, and paste in the following code. I used yyyy-mm as the sheet name format, but you could use a different format.
For example, to see the full month name, use mmmm as the format.
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 the Code Automatically
To make the code run automatically, when the workbook opens, you’ll create a Workbook_Open event.
Paste the following code on the ThisWorksheet module:
Private Sub Workbook_Open() AddMonthWkst End Sub
Download the Sample File
To see the workbook and the Add Worksheet code, you can download the Add Worksheet sample file.
The file is in Excel 2007 format, and zipped. It contains macros, so you’ll need to enable them, to see the code working.
Watch the Add Worksheet Automatically Video
To see the steps for creating the code, and making it run automatically, you can watch this Excel Video Tutorial.
_____________