Copying Excel Sheet Multiple Times

[Zedrick13]

Is there faster way of creating multiple copies of a certain sheet on the same workbook rather than the conventional way of copy a sheet one at a time?

Regards,
Zed

 

[Logit]

.
There are a number of ways to accomplish the goal. Here is one.

Save a workbook with just one sheet named TEMPLATE.

In A1 enter "New Sheet Name"
In A2 is the location where you will enter the name of the new sheet you are adding.
Place a command button on the TEMPLATE sheet connect to the macro below.

You can create one new sheet at a time. The

Option Explicit


Sub CreateNew()
Dim wsRecipes As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Dim i As Long
Dim wsIndex
On Error Resume Next




With ThisWorkbook                                               'keep focus in this workbook
Application.CopyObjectsWithCells = False
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    wasVISIBLE = (wsTEMP.Visible = xlSheetVisible)              'check if it's hidden or not
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetVisible      'make it visible
      
    Application.ScreenUpdating = False                          'speed up macro


    wsTEMP.Copy After:=.Sheets(.Sheets.Count)                   '...create it from template
    
    wsIndex.Activate                                            'return to the master sheet
    If Not wasVISIBLE Then wsTEMP.Visible = xlSheetHidden       'hide the template if necessary
    Application.ScreenUpdating = True                           'update screen one time at the end


Application.CopyObjectsWithCells = True
End With
Sheets(Sheets.Count).Select


Dim tabname As String
tabname = Sheets("Template").Range("A2").Value
ActiveSheet.Name = tabname
ActiveSheet.Range("A1:A2").Value = ""
Sheets("Template").Range("A2").Value = ""
MsgBox "New sheet created. "
End Sub

 

[My Aswer Is This]

Try this:

This script assumes you want to make copies of a sheet named "Master"
Modify as needed.

Sub Copy_Sheets()
'Modified 1/21/2019 9:34:49 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim ans As Long
ans = InputBox("Enter number copies you want")
    For i = 1 To ans
        Sheets("Master").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Master" & ActiveSheet.Index
    Next
Application.ScreenUpdating = True
End Sub

 

[Zedrick13]

Thank you, everyone.

 

[My Aswer Is This]

Which script worked for you?

Thank you, everyone.

 

[Logit]

You are welcome.