How To Add Sheet In Excel Using Vba

Adding a Sheet in Excel using VBA

To add a sheet in Excel using VBA (Visual Basic for Applications), you can use the Add method of the Sheets collection. Here is an example:

    
      Sub AddSheet()
          Dim newSheet As Worksheet
          Set newSheet = ThisWorkbook.Sheets.Add

          ' Rename the new sheet
          newSheet.Name = "New Sheet"
      End Sub
    
  

In the above example, we define a subroutine named AddSheet. We declare a variable newSheet of type Worksheet to store the reference of the new sheet. Then, we use the Add method of the Sheets collection of the workbook to add a new sheet and assign it to the newSheet variable.

To rename the new sheet, we can access the Name property of the newSheet and set it to the desired name (in this case, “New Sheet”).

This example adds a new sheet to the end of the sheets collection. If you want to add the sheet at a specific position, you can use the Add method with the Before or After parameter to specify the sheet before or after which the new sheet should be added.

    
      ' Add the new sheet after the first sheet
      Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(1))
    
  

By default, the new sheet will be added as the last sheet in the workbook.