30 Advanced Excel Tips for 2026

Excel Champs / VBA / How to Write a VBA Code to Create a New Sheet in Excel (Macro)
By Puneet Gogia (Microsoft MVP)

How to Write a VBA Code to Create a New Sheet in Excel (Macro)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Sheets.Add inserts a new worksheet before the active sheet. To place it and name it in one line, use Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary", which adds it at the end.

Use Worksheets.Add Before:=Worksheets(1) to put it first, Worksheets.Add Count:=3 to add several, and Set ws = Worksheets.Add to keep a reference so you can work with the new sheet straight away.

Naming fails with error 1004 if the name is already used, is blank, is longer than 31 characters, or contains : / ? * [ ]. Check first when the name comes from a cell or a list:

Function SheetExists(n As String) As Boolean
    On Error Resume Next
    SheetExists = Not ThisWorkbook.Sheets(n) Is Nothing
End Function

Read the name into a variable before you add the sheet, and qualify the source sheet: n = Worksheets("List").Range("A1").Value. A bare Range("A1") reads whichever sheet is active, and after Sheets.Add that’s the new, empty one.

VBA ADD SHEET | Write a Macro (VBA CODE) to Add a New Worksheet in Excel

Once you start learning VBA, one of the coolest things you can do is to write a VBA code to insert a new worksheet in a workbook.

Well, there is already a shortcut key to insert a new worksheet (Shift + F11), or you can also use the normal option, but the benefit of using a VBA code is that you can add multiple worksheets with a single click, and you can also define where you want to add them.

For this, you need to use the Sheets.Add method, and in this post, we will be learning how to use it to add one or more worksheets in a workbook.

Sheets.Add Method

Sheets.Add ([Before], [After], [Count], [Type])
  • Before: To add a new sheet before a sheet.
  • After: To add the new sheet after a sheet.
  • Count: Number of sheets to add.
  • Type: Type of the sheet you want to add (LINK)

Write a VBA Code to ADD a New Sheet in a Workbook

Open the visual basic editor and follow these steps.

  • First, you need to enter the Sheets.Add method.
  • Then you need to define the place to add the new sheet (Before or After).
  • The next thing is to enter the count of worksheets.
  • In the end, enter the type of sheet. All four arguments are optional, and you can use either Before or After, not both.
add-sheets-add-method-vba

Different Ways to Add New Sheets in a Workbook Using a VBA Code

Below you have different ways to add a new sheet to a workbook:

1. Add a Single Sheet

To add a single sheet, you can use the below code, where you don’t specify any argument.

Sub SheetAddExample1()
ActiveWorkbook.Sheets.Add
End Sub

This code tells Excel to add a sheet in the active workbook, but as you don’t have any argument, it will use the default values and add one worksheet (xlWorksheet) before the active sheet.

Here’s one more way to write this, check out the below code.

Sub SheetAddExample2()
Sheets.Add
End Sub

As you are already in the active workbook, you can use the above code as well. It does the same thing.

2. Add Multiple Sheets

To add multiple sheets in one go, you just need to define the COUNT argument with the number of sheets you want to add.

Sub AddSheets3()
Sheets.Add Count:=5
End Sub

Now the count of the sheets that you have defined is 5, so when you run this code, it instantly adds five new sheets to the workbook.

3. Add a Sheet with a Name

If you want to rename the sheet after adding it, you can use the following code:

Sub AddNewSheetswithNameExample1()
Sheets.Add.Name = "myNewSHeet"
End Sub

In the above code, we have used the Name property of the new sheet (LINK), which helps you to specify the name of a sheet. Sheets.Add returns the new sheet, so you can set its name in the same line.

4. Add a Sheet with a Name from a Cell

You can also take the value to use as the sheet’s name from a cell.

Sub AddNewSheetswithNameExample2()
Sheets.Add.Name = Range("A1")
End Sub

In the above code, cell A1 is used to get the name for the new sheet.

5. Add a Sheet After/Before a Specific Sheet

With the Before and After arguments of Sheets.Add, you can specify the sheet to add a new sheet before or after.

Sub AddSheetsExample5()
Sheets.Add Before:=Worksheets("mySheet")
Sheets.Add After:=Worksheets("mySheet")
End Sub

Now in the above code, you have two lines of code: one uses the Before argument and the other uses the After argument of the Sheets.Add method. So, when you run this code, it adds two sheets, one before and one after “mySheet”.

6. Add a New Sheet at Beginning

By using the Before argument, you can also add a sheet at the beginning of the sheets that you have in the workbook.

So basically, what we are going to do is we’re going to specify the sheet number instead of the sheet name.

Sub AddSheetsExample6()
Sheets.Add Before:=Sheets(1)
End Sub

In the above code, you have used sheet number (1) that tells VBA to add the sheet before the sheet which is in the first position among all the sheets. In this way, it will always add the new sheet at the beginning.

7. Add a New Sheet at the End (After the Last Sheet)

To add a new sheet at the end, you need to write the code in a different way. For this, you need to know how many sheets there are in the workbook so that you can add a new sheet at the end.

Sub AddSheetsExample8()
Sheets.Add After:=Sheets(Sheets.Count)
End Sub

In the above code, Sheets.Count returns the count of the sheets that you have in the workbook, and as you have defined the After argument, it adds the new sheet after the last sheet in the workbook.

8. Add Multiple Sheets and use Names from a Range

The following code counts rows from the range A1:A7. After that, it loops to add sheets according to the count from the range and uses values from the range to name the sheet while adding it.

Sub AddSheetsExample9()
Dim sheet_count As Integer
Dim sheet_name As String
Dim i As Integer
sheet_count = Range("A1:A7").Rows.Count
For i = 1 To sheet_count
  sheet_name = Sheets("mySheet").Range("A1:A7").Cells(i, 1).Value
  Worksheets.Add().Name = sheet_name
Next i
End Sub

As each new sheet is added before the active sheet (the one just added), the sheets end up in reverse order of the list. Use Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheet_name if you want them in the same order as the list.

But with the above code, there could be a chance that the sheet name you want to add already exists or you have a blank cell in the name range.

In that case, you need to write a code that can verify if the sheet with the same name already exists or not and whether the cell from where you want to take the sheet name is blank or not.

Only if both conditions are fulfilled should it add a new sheet. Let me put it in two steps:

First, you need to write an Excel User Defined Function to check if a sheet with the same name already exists or not.

Function SheetCheck(sheet_name As String) As Boolean
Dim ws As Worksheet
SheetCheck = False
 
For Each ws In ThisWorkbook.Worksheets
 
    If LCase(ws.Name) = LCase(sheet_name) Then
    
        SheetCheck = True
        
    End If
 
Next
 
End Function

Second, you need to write a code using this function, and that code should also check if the name cell is blank or not. The function compares the names in lowercase, because Excel treats “Sales” and “sales” as the same sheet name.

Sub AddMultipleSheet2()
Dim sheet_count As Integer
Dim sheet_name As String
Dim i As Integer
sheet_count = Range("A1:A7").Rows.Count
For i = 1 To sheet_count
    sheet_name = Sheets("mySheet").Range("A1:A7").Cells(i, 1).Value
    
    If SheetCheck(sheet_name) = False And sheet_name <> "" Then
    Worksheets.Add().Name = sheet_name
    End If
Next i
End Sub

Now in the above code, you have used the VBA IF Statement and in this statement, you have the sheet check function which checks for the sheet name and then you have a condition to check if the name cell has a blank value.

Sheet Name Rules

If the name you give to the new sheet breaks one of these rules, VBA shows run-time error 1004, and the new sheet stays with a default name like Sheet4:

  • Length: a sheet name can have up to 31 characters and can’t be blank.
  • Characters: it can’t have \ / ? * [ ] or : and can’t start or end with an apostrophe.
  • Unique: it can’t be the same as another sheet in the workbook (upper and lower case count as the same), and it can’t be “History”.

Add a Sheet and Work with It

Sheets.Add returns the new sheet, so you can store it in a variable and use it in the rest of the code. The following code adds a sheet at the end of another open workbook, names it, and enters a heading:

Sub AddSheetAndUseIt()
Dim ws As Worksheet
With Workbooks("Book2.xlsx")
Set ws = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
End With
ws.Name = "Report"
ws.Range("A1").Value = "Monthly Report"
End Sub

A new sheet becomes the active sheet. If you want to go back to the sheet you were on, store ActiveSheet in a variable before adding the sheet and activate it again in the end.

Frequently Asked Questions

What is the difference between Sheets.Add and Worksheets.Add?

Both add a worksheet by default. Sheets includes chart sheets as well, so with Sheets.Add you can also add a chart sheet with Type:=xlChart, while Worksheets.Add is only for worksheets.

Why do I get error 1004 when adding a sheet?

Usually, the name breaks one of the sheet name rules above or a sheet with that name already exists. It can also happen when the workbook structure is protected, which doesn’t allow adding sheets.

Can I add a sheet with a name that includes today’s date?

Yes, but format the date without slashes, which aren’t allowed in sheet names, like Sheets.Add.Name = Format(Date, “dd-mm-yyyy”).

Sample File

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel