When I first delved into the world of VBA programming, I discovered a really neat trick. I learned how to write a bit of code that lets me insert a new worksheet into an Excel workbook. Sure, you might know the keyboard shortcut or the standard menu option to add a single sheet, but with VBA, I can create multiple sheets in an instant and have total control over their placement within the workbook.

The magic happens with the Sheets.Add method. It’s a game-changer for anyone who works with Excel regularly. In this article, I’m going to take you through the process of using this method to quickly add one or more worksheets exactly where you want them. Whether you’re new to Excel VBA or looking to streamline your workflow, getting to grips with this could save you a ton of time.

Sheets.Add Method

' Basic syntax to add a new worksheet
Sheets.Add

' Add a new sheet with parameters
Sheets.Add(Before:=WorksheetObject, After:=WorksheetObject, Count:=1, Type:=XlSheetType)

Parameters:

  • Before: Optional. The sheet before which the new sheet is added.
  • After: Optional. The sheet after which to add the new sheet.
  • Count: Optional. The number of sheets to add. Default is 1.
  • Type: Optional. Specifies the sheet type. Visit Microsoft’s sheet type enumeration for options.

Return Value:

  • Adds a new worksheet(s) and returns a Worksheet object representing it.

Usage Notes:

  • If both Before and After are omitted, the new sheet is inserted before the active sheet.
  • Omitting Count and Type will add a single worksheet.

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

Sub AddSheetExample()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "NewSheet"
    ws.Activate
    MsgBox "New sheet added!"
End Sub
  • Code Breakdown:
    • Dim ws As Worksheet – declares a worksheet variable.
    • Set ws = – assigns the new sheet to the variable.
    • Sheets.Add – adds the new sheet.
    • After:= – defines the location after the last sheet.
    • "NewSheet" – sets the name of the new sheet.
    • ws.Activate – brings the newly created sheet into view.
    • MsgBox – displays a confirmation message.

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

1. Add a Single Sheet

When I need to add a single sheet to my workbook, it’s pretty straightforward. I use:

Sub SheetAddExample1()
ActiveWorkbook.Sheets.Add
End Sub

This adds a worksheet type (xlWorksheet) before the active sheet. Alternatively, I can simply use:

Sub SheetAddExample2()
Sheets.Add
End Sub

2. Add Multiple Sheets

For adding multiple sheets, I specify the number with the Count argument:

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

This adds five new sheets to my workbook in one go.

3. Add a Sheet with a Name

Whenever renaming a newly added sheet is on my plan, this is the way to go:

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

Using the .Name property sets the name right after adding the sheet.

4. Add a Sheet with a Name from a Cell

I like getting creative by using cell values as sheet names:

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

Here, whatever I have in cell A1 becomes the new sheet’s name.

5. Add a Sheet After/Before a Specific Sheet

When I’m aiming to add a sheet relative to a specific sheet, I tweak the Before and After arguments:

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

This snippet adds one sheet before and one after “mySheet”.

6. Add a New Sheet at Beginning

To insert a sheet at the very start, I use:

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

The (1) indicates the first position among all worksheets.

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

To append a sheet to the end of my workbook, for instance, I determine how many sheets there are and then add a new one:

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

The Sheets.Count ensures the new sheet is the last one.

8. Add Multiple Sheets and use Names from a Range

Let’s say I want to name sheets after a list in a range:

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

I must be careful, though. Sheets with existing names will cause an error. So, I write:

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

Sub AddMultipleSheet2()
Dim sheets_count As Integer
Dim sheet_name As String
Dim i As Integer

sheet_count = Worksheets("mySheet").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

This setup ensures no duplicates or blanks are named, thanks to a slick IF statement.

So with the tricks up my sleeve and a bit of VBA magic, organizing and customizing my Excel workbook by adding sheets is a piece of cake. And hey, to tie it all together beautifully, remember there’s a Sample File available to play around with these codes for a hands-on experience!

Related Tutorials

Here’s a handy set of links to help you master sheet manipulation in VBA for Excel:

And for the curious ones, dive deep into VBA Worksheet Objects to manipulate sheets at a more fundamental level. Not sure if a sheet exists? Here’s how to check for a sheet’s existence.

Similar Posts