How To Add Multiple Sheets In Excel Using Vba

by

How to Add Multiple Sheets in Excel Using VBA

VBA (Visual Basic for Applications) can be used to automate various tasks in Microsoft Excel, including the creation of multiple sheets. Here is an example of how to add multiple sheets to an Excel workbook using VBA:

Sub AddSheets()
    Dim i As Integer
    Dim sheetName As String
    
    For i = 1 To 5 ' Change the number of sheets as per your requirements
        sheetName = "Sheet " & i
        
        ' Add a new sheet
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
        
        ' Enter some sample data in the newly created sheet
        With Worksheets(sheetName)
            .Range("A1").Value = "Example Data"
            .Range("A2").Value = "Sheet " & i
        End With
    Next i
End Sub

In the above VBA code, a loop is used to add five sheets to the workbook. The name of each sheet is set using the variable ‘sheetName’. The ‘Worksheets.Add’ method is used to add a new sheet after the last sheet in the workbook. The ‘Worksheets.Count’ property is used to determine the position to add the new sheet.

After adding a new sheet, sample data is entered into the sheet using the ‘Range’ property. In this example, the text “Example Data” is placed in cell A1, and the name of the sheet (e.g., “Sheet 1”, “Sheet 2”) is placed in cell A2.

The above VBA code can be executed by following these steps:

  1. Open an Excel workbook.
  2. Press ALT+F11 to open the VBA editor.
  3. Insert a new module by clicking on ‘Insert’ > ‘Module’.
  4. Paste the VBA code into the module.
  5. Close the VBA editor.
  6. Press ALT+F8 to open the ‘Macro’ dialog box.
  7. Select the ‘AddSheets’ macro from the list and click ‘Run’.

After executing the VBA code, the specified number of sheets (in this example, five sheets) will be added to the workbook, and each sheet will contain the sample data as defined in the code.

This is just one example of how to add multiple sheets in Excel using VBA. Depending on your specific requirements, you can customize the code accordingly.

Leave a comment