How to duplicate a sheet multiple times using VBA?

To duplicate a sheet multiple times in Excel using VBA, you can create a VBA macro. The following example demonstrates how to duplicate a specific worksheet multiple times:

   Sub DuplicateSheetMultipleTimes()
       Dim i As Integer
       Dim numCopies As Integer
       Dim sheetName As String
       Dim sourceSheet As Worksheet
       
       ' Set the sheet you want to duplicate
       sheetName = "Sheet1"  ' Change this to the name of your sheet
       
       ' Set the number of copies you want to make
       numCopies = 5  ' Adjust this number as needed

       ' Error handling if sheet isn't found
       On Error Resume Next
       Set sourceSheet = ThisWorkbook.Sheets(sheetName)
       On Error GoTo 0
       
       If sourceSheet Is Nothing Then
           MsgBox "Sheet named " & sheetName & " not found!", vbExclamation
           Exit Sub
       End If
   
       For i = 1 To numCopies
           ' Copy the sheet
           sourceSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
           
           ' Optionally, rename the new sheets
           With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
               .Name = sheetName & "_" & i
           End With
       Next i
   End Sub

Open Excel and press `ALT` + `F11` to open the VBA editor.
Insert a new module by going to `Insert` > `Module`.
Copy and paste the following code into the module:
Adjust the `sheetName` variable to the name of the sheet you wish to duplicate, and set the `numCopies` variable to the number of copies you want to create.
Run the macro by pressing `F5` or by going to `Run` > `Run Sub/UserForm`.

This macro will create the specified number of copies of the selected worksheet, appending a number to the name of each new sheet to distinguish them. Make sure that the new sheet names do not exceed Excel’s character limit for sheet names and that they are unique, otherwise, Excel will throw an error due to naming conflicts.

facebooklinkedin