Automatically Add Sheet When Excel Opens

imageOn 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.

_____________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.