How to write VBA code to create a new worksheet in Excel (macro) (2023)

Content to hide

Method Sheets.Add

Write VBA code to add a new worksheet to a workbook

Different ways to add new worksheets in a workbook using a VBA code

1. Add a single sheet

3. Add a worksheet with a name

4. Add a worksheet with a cell name

5. Add a sheet after/before a specific sheet

6. Add a new worksheet at the beginning

7. Add a new sheet at the end (after the last sheet)

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

8. Add multiple worksheets and use range names

(Video) VBA to Create New Workbook - Excel VBA Macro Example

More spreadsheet tutorials

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

Well, there is already a hotkey to insert a new worksheet or you can also use the normal option, but the benefit of using a VBA code is that you can add multiple worksheets with one click and you can also define where you want add this.

For that, you need to use the Sheets.Add method, and in this post we'll learn how to use it to add one or more sheets to a workbook.

Method Sheets.Add

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

Write VBA code to add a new worksheet to a workbook

Open the basic visual 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 sheet count.
  • In the end, the type of sheet.
How to write VBA code to create a new worksheet in Excel (macro) (1)

Different ways to add new worksheets in a workbook using a VBA code

Here are different ways to add a new worksheet to a workbook:

1. Add a single sheet

To add a single sheet, you can use the code below where you have not specified any arguments.

(Video) Create a New Sheet and Rename Worksheet using VBA in Excel 2010

Sub SheetAddExample1()ActiveWorkbook.Sheets.AddEnd Sub

This code tells Excel to add a worksheet to the active workbook, but since it has no arguments, it will use the default values ​​and add a worksheet (xlWorksheet) before the active worksheet.

Here's one more way to write this, see the code below.

Sub SheetAddExample2() Sheets.AddEnd Sub

Since you are already in the active workbook, you can also use the following code. It does the same thing.

2. Add multiple worksheets

To add multiple sheets at once, simply set the COUNT argument to the number of sheets you want to add.

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

Now the worksheet count you defined is 5, so when you run this code, it will instantly add the five new sheets in the workbook.

3. Add a worksheet with a name

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

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

In the code above, we use the name object (LINK) that helps specify the name of a worksheet.

4. Add a worksheet with a cell name

You can also get the value to use as the worksheet name of a cell.

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

In the above code, cell A1 is used to get the name of the new worksheet.

5. Add a sheet after/before a specific sheet

Since these arguments are already there in Sheets.Add, where 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 code above, you have two lines of code that you used before and after an argument in the Sheet.Add method. So when you run this code, it adds two sheets, one before and one after "mySheet".

6. Add a new worksheet at the beginning

By using the above argument, you can also add a sheet to the beginning of the sheets you have in the workbook.

Basically, what we're going to do is specify the sheet number instead of the sheet name.

(Video) Excel VBA: Create New Worksheet For EACH UNIQUE ITEM in List & Copy Record

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

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

7. Add a new sheet at the end (after the last sheet)

To add a new worksheet at the end, you have to write the code in a different way. So for that you need to know how many sheets are in the workbook so you can add a new sheet at the end.

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

In the above code, Sheet.Count returns the count of sheets you have in the workbook, and by setting the after argument, adds the new sheet after the last sheet in the workbook.

8. Add multiple worksheets and use range names

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

Sub AddSheetsExample9()Dim Sheets_count As IntegerDim sheet_name As StringDim i As Integersheet_count = Range("A1:A7").Rows.CountFor i = 1 To sheet_count nombre_hoja = Sheets("mySheet").Range("A1:A7"). Cells(i, 1).Value Worksheets.Add().Name = sheet_nameNext iEnd Sub

But with the code above, it is possible that the sheet name you want to add already exists or it may have a blank cell in the name range.

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

If both conditions are met only then you must add a new sheet. Let me break it down into two-step steps:

Firstyou have to write aExcel User Defined Functionto check whether a worksheet with the same name already exists or not.

Función SheetCheck(sheet_name As String) As BooleanDim ws As WorksheetSheetCheck = Falso para cada ws en ThisWorkbook.Worksheets If ws.Name = sheet_name Then SheetCheck = True End If Next End Function

Second, you have to write a code with this function and that code also has to check if the name cell is blank or not.

Sub AddMultipleSheet2()Dim Sheets_count As IntegerDim sheet_name As StringDim i As Integersheet_count = Range("A1:A7").Rows.CountFor i = 1 To sheet_count nombre_hoja = Sheets("mySheet").Range("A1:A10"). Cells(i, 1).Value If SheetCheck(sheet_name) = False And sheet_name <> "" Then Worksheets.Add().Name = sheet_name End IfNext iEnd Sub

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

sample file

  • How to COUNT worksheets in Excel using VBA
  • How to loop through all sheets in a workbook using VBA
  • How to check if a sheet exists in a workbook using VBA
  • How to DELETE an entire worksheet in Excel using VBA
  • How to PROTECT and Unprotect a worksheet in Excel using VBA
  • How to DELETE a WORKSHEET using VBA
  • How to hide/show a worksheet in Excel using VBA
  • How to rename a spreadsheet in Excel using VBA
  • How to COPY a worksheet in Excel using VBA
  • How to activate a worksheet using VBA
(Video) VBA Code to Add New Sheet and Rename - Excel VBA Example by ExcelDestination
  • vba worksheetExcel VBA ExamplesVBA tutorial

Videos

1. Excel VBA Macro to Create Workbook for Each Worksheet
(Chester Tugwell)
2. Excel VBA Command | Insert new sheet with VBA Code | Excel VBA Lesson -19 | Excel VBA for beginners
(Computer Gyan Guruji)
3. VBA/Macro Course - 3 Ways to Create New Workbooks & Save and Close Them Using a Macro
(TeachExcel)
4. VBA to Create New Sheets based on a List - Excel VBA Example by Exceldestination
(Excel Destination)
5. Excel VBA - Write a Simple Macro
(Tutorials Point)
6. Excel VBA - Create Workbook
(Tutorials Point)
Top Articles
Latest Posts
Article information

Author: Nicola Considine CPA

Last Updated: 02/01/2023

Views: 6768

Rating: 4.9 / 5 (49 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Nicola Considine CPA

Birthday: 1993-02-26

Address: 3809 Clinton Inlet, East Aleisha, UT 46318-2392

Phone: +2681424145499

Job: Government Technician

Hobby: Calligraphy, Lego building, Worldbuilding, Shooting, Bird watching, Shopping, Cooking

Introduction: My name is Nicola Considine CPA, I am a determined, witty, powerful, brainy, open, smiling, proud person who loves writing and wants to share my knowledge and understanding with you.