Add a new worksheet to a workbook using VBA.

The Add method of the Workheets collection is used to create a new worksheet. The new worksheet becomes the active sheet. Alternatively, you can use Sheets collection which gives you the same result. Similarly, you can use Charts.Add to add a chart.

In its simplest form it is called like this:


ActiveWorkbook.Worksheets.Add 'abreviated: Worksheets.Add or Sheets.Add

Note: To keep the sample code short we use the ActiveWorkbook method to provide the parent Workbook object whose Sheets collection is being used.

Tip: In real code using explicitly declared and assigned object variables is preferred, in the current case: Dim wb As Workbook: Set wb = ActiveWorkbook , quite likely with another object assignment expression.

The Sheets collection object in the above is obtained from the Workbook Worksheets procedure.

The procedure returns a value of type Worksheet which is optionally assigned to variable ws .


Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets.Add(Type:=xlWorksheet)

Note: In case there are more than one properties and/or methods to be called from the newly created item they are often placed in a With statement block. Using the With syntax makes the code structure clearer and has performance benefits.

If Before and After are both omitted, the new sheet is inserted before the active sheet.

Before / After: where the new sheet will be inserted

Use as the Before or After argument a sheet object to specify where the new sheet is to added. As you need to specify a sheet from the same workbook as you will be adding to, it makes sense to use a Workbook variable: wb

Add a new sheet at beginning of workbook


Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Sheets.Add Before:=wb.Sheets(1)

wb.Sheets(1) is the way to get the first sheet in the workbook - where you want to insert Before.

Add a new sheet after last

You can use the following code to add a new sheet to end of a workbook.


Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Sheets.Add After:=wb.Sheets(wb.Sheets.Count)

wb.Sheets.Count gives you the index number of last sheet in the workbook - where you want to insert After.

Count

The number of sheets to be added.


ActiveWorkbook.Worksheets.Add(Count:=1)

Warning: The default value for Count is the number of selected sheets. To get an predictable number of sheets added it seems wise to explicitly specify this value.

The Type argument: Worksheet, Chart or template

If not specified a worksheet is inserted (except in the case of Charts.Add, obviously.)


ActiveWorkbook.Sheets.Add 'add a Worksheet
ActiveWorkbook.Sheets.Add(Type:=xlChart) 'a chart

Trying Worksheets.Add Type:=xlChart gives error 1004: Method 'Add' of object 'Sheets' failed

Create a new sheet from a template

The ability to use a file as the source for the sheets to be added is powerful. It allows you to:

supply a complete formatted layout, possibly involving multiple sheets - a template
import data from a source file

The source file need not be an Excel template, any file type that can be opened by excel will do, e.g.


Sheets.Add Type:="C:\Code VBA\Demo\Layout.xlsx"
Sheets.Add Type:="C:\Code VBA\Demo\Data240614.csv"

Add a new sheet and name it

First create the new sheet and then rename it.


Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets.Add
Dim strName As String: strName = "new name"
ws.Name = strName

Create a new sheet with name if not exist

First check if the sheet exists. If not you can create it.


Dim wb As Workbook
Set wb = ActiveWorkbook
Dim strSheetName As String: strSheetName = "the name"
If Not SheetExists(SheetName:=strSheetName, Workbook:=wb) Then
	Set ws = ActiveWorkbook.Worksheets.Add
	ws.Name = strSheetName
End If

Tip: The function SheetExists is included in the code library included in Code VBA and would be found under menu Object » wb » Fragments » .

Create a new sheet with today's date

Use the Format or the FormatDateTime function to easily obtained the required name string.


Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets.Add
Dim strName As String: strName = "new name"
ws.Name = Format(Date, "yymmdd")

Create a new sheet based on cell value

The code below shows how the new sheet gets it value from a cell on a worksheet already present.


Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsWithValue As Worksheet
Dim strSheet As String: strSheet = "Sheet1"
Set wsWithValue = wb.Sheets(Index:=strSheet)
Dim ws As Worksheet
Set ws = wb.Worksheets.Add
Dim strRange As String: strRange = "A1"
Dim rng As Range
Set rng = wsWithValue.Range(Cell1:=strRange)
Dim strName As String: strName = rng.Value
ws.Name = strName

Note: The above code was created using Code VBA editor add-in. The code could have been made more compact - for example I could have inserted the range address directly without having a variable strRange - but here I chose for having all steps are maximally explicit, which also makes the code most versatile and extensible.

Create a new sheet and copy data

Finally, you will most probably add some data into the new worksheet. Below I assume the data comes from another datasource first opened


Dim wbDestination As Workbook
Set wbDestination = ActiveWorkbook
Dim wsDestination As Worksheet
'add the new worksheet
Set wsDestination = wbDestination.Worksheets.Add
Dim strRangeDestination As String: strRangeDestination = "B2"
Dim rngDestination As Range
Set rngDestination = wsDestination.Range(Cell1:=strRangeDestination)

'get acces to the source
Dim wbSource As Workbook
Set wbSource = Workbooks.Open(Filename:="C:\Code VBA\Demo\Data.xlsx")
Dim wsSource As Worksheet
Dim strSheetSource As String: strSheetSource = "Sheet1"
Set wsSource = wbSource.Sheets(Index:=strSheetSource)
Dim strRangeSource As String: strRangeSource = "A1"
Dim rngSource As Range
Set rngSource = wsSource.Range(Cell1:=strRangeSource)
'copy the value
rngDestination.Value = rngSource.Value

Author: Mark Uildriks

Reference: Sheets.Add(Excel) | Microsoft Learn