How To Add Sheet In Excel Vba

by

How to Add a Sheet in Excel VBA

Adding a sheet in Excel VBA can be easily achieved using the Worksheets.Add method. This method allows you to add a new sheet to the workbook at a specific position or after a specific sheet.

Example 1: Adding a Sheet at the End of the Workbook

    
      <script language="vba">
        Sub AddSheetAtEnd()
          ' Activate the workbook you want to add the sheet to
          ThisWorkbook.Activate
          
          ' Add a new sheet at the end of the workbook
          Dim newSheet As Worksheet
          Set newSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
          
          ' Rename the new sheet
          newSheet.Name = "New Sheet"
        End Sub
      </script>

This example uses the Worksheets.Count property to determine the position where the new sheet should be added after. By adding After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) as an argument to the Worksheets.Add method, the new sheet will be inserted after the last sheet in the workbook. Finally, the Name property is used to rename the new sheet as “New Sheet”.

Example 2: Adding a Sheet at a Specific Position

    
      <script language="vba">
        Sub AddSheetAtPosition()
          ' Activate the workbook you want to add the sheet to
          ThisWorkbook.Activate
          
          ' Add a new sheet at a specific position (e.g., after Sheet2)
          Dim newSheet As Worksheet
          Set newSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets("Sheet2"))
          
          ' Rename the new sheet
          newSheet.Name = "New Sheet"
        End Sub
      </script>

In this example, the new sheet is added after a specific sheet named “Sheet2”. By passing After:=ThisWorkbook.Worksheets("Sheet2") as an argument to the Worksheets.Add method, the new sheet will be inserted after “Sheet2”. The Name property is then used to rename the new sheet.

Remember to activate the workbook you want to add the sheet to before executing the code. This can be done using the ThisWorkbook.Activate statement.

Leave a comment