[Excel VBA] How to Add a New Worksheet and Specify Its Name and Position

2025年12月27日

Introduction

In Excel VBA, there are frequent scenarios where you need to add a new worksheet during macro execution—whether to output summary results or to create a temporary workspace.

While the Worksheets.Add method makes it easy to add a sheet, learning how to change its name and insert it at a specific location (such as the beginning or the end of a workbook) allows for much more sophisticated operations.

In this article, I will explain everything from the basics of adding a new sheet to advanced techniques for controlling its insertion position.

Basic Sheet Addition and Naming

First, let’s look at the standard way to add a new sheet and assign a name to it. The key here is to store the newly created sheet in an object variable.

Completed VBA Code

Sub AddAndNameNewSheet()

    ' Declare a variable to store the Worksheet object
    Dim newSheet As Worksheet
    
    ' Add a new worksheet and store the object in the variable
    ' This ensures you can reliably manipulate the sheet you just added
    Set newSheet = ThisWorkbook.Worksheets.Add
    
    ' Change the name of the new sheet via the object variable
    newSheet.Name = "VBA_Generated_Report"
    
    MsgBox "Added a sheet named '" & newSheet.Name & "'."

End Sub

Key Points of the Code

Set newSheet = Worksheets.Add: The Worksheets.Add method adds a new sheet and simultaneously returns the created Worksheet object. By using Set to store it in a variable (newSheet), you can precisely reference the “sheet you just added” in subsequent lines of code.
newSheet.Name = “…”: By using an object variable, you can directly and safely modify properties (in this case, the name) without relying on ambiguous references like ActiveSheet.Name.

Specifying the Sheet Position

The Worksheets.Add method allows you to freely control the insertion position by using the Before or After arguments.

Before Argument: Insert Before a Specified Sheet

The new sheet will be added to the left of the specified sheet.

Sub AddSheetBefore()
    ' Add a new sheet before "Sheet1"
    Worksheets.Add Before:=Worksheets("Sheet1")
End Sub

After Argument: Insert After a Specified Sheet

The new sheet will be added to the right of the specified sheet.

Sub AddSheetAfter()
    ' Add a new sheet after "Sheet1"
    Worksheets.Add After:=Worksheets("Sheet1")
End Sub

[Advanced] Adding a Sheet to the Very End of the Workbook

One of the most common practical requirements is to “add a sheet to the far right (end) of the workbook.” This is achieved by combining the After argument with Worksheets.Count (the total number of sheets).

Sub AddSheetToEnd()
    Dim lastSheet As Worksheet
    Dim newReportSheet As Worksheet
    
    ' Identify the last sheet in the current workbook
    Set lastSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    
    ' Add a new sheet after the last sheet
    Set newReportSheet = ThisWorkbook.Worksheets.Add(After:=lastSheet)
    
    newReportSheet.Name = "Final_Report"
    MsgBox "Added the '" & newReportSheet.Name & "' sheet to the end of the workbook."
End Sub

By using Worksheets(Worksheets.Count), you identify the current last sheet and instruct the macro to place the new sheet After it.

Summary

The basics and applications of adding new sheets in VBA are as follows:

Basics: Store the added sheet in a variable using Set mySheet = Worksheets.Add.
Positioning: Use the Before or After arguments.
- Worksheets.Add Before:=Worksheets("ExistingSheetName")
- Worksheets.Add After:=Worksheets("ExistingSheetName")
Adding to the End: Use Worksheets.Add After:=Worksheets(Worksheets.Count).