Specify Sheet

[Tech56]

Hi,

Does someone know how to change the code to specify worksheet 'Template' instead of active sheet?

Thanks

Sub Create()

    Dim i As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    xNumber = InputBox("Enter number of times to copy the current sheet")
    For i = 1 To xNumber
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Name = "R-" & i
    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
    Call ListSheets
    
End Sub

 

[Marc L]

Hi,​

​

the best is to use the sheet's CodeName if the code is located in the same workbook or use the sheet's name like Sheets("name") …​

​

 

[Tech56]

Hi can you modify the code please ?

 

[Marc L]

I can't do nothing with such a 'code' and I won't guess anything ! Just use the sheet reference when you need …​

​

Another way to learn is to activate the Macro Recorder and operate manually : you will get your own starter.​

​

 

[Tech56]

I want to copy the sheet 'Template' multiple times and name the new sheets R-1, R-2, etc.

Can you derive a code since you don't like the one I provided?

Thank you

 

[Tech56]

Hi I found this one but it doesn't name the sheets:

Sub Copier ()
Dim x As Integer
x = InputBox("Enter number of times to copy Template")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Template").Copy _
After:=ActiveWorkbook.Sheets("Template")
Next
End Sub

 

[Tech56]

Never mind I will figure out on my own.