Copy template to a New Sheet then rename and increment

chesterrae

Board Regular
Joined
Dec 23, 2015
Messages
51
I have found similar topic that deal with copying an entire sheet in one workbook and pasting it to a NEW sheet while the value on the specific cell increments and adds to the name of the new sheet created. However, the code provided was incomplete and missed the copying of the entire template sheet to new sheet. It only increments and rename the sheet created.

here's the given code:

Sub AddWs()
Dim i As Long, wsName As String, temp As String
Worksheets.Add after:=Worksheets(Worksheets.Count)
wsName = "App1"
If WorksheetExists(wsName) Then
temp = Left(wsName, 3)
i = 1
wsName = temp & i
Do While WorksheetExists(wsName)
i = i + 1
wsName = temp & i
Loop
End If
ActiveSheet.Name = wsName
ActiveSheet.Range("A1").Value = i
End Sub

Function WorksheetExists(wsName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(wsName).Name = wsName
On Error GoTo 0
End Function
Please help.
Thank you!

 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Replace this line...
VBA Code:
Worksheets.Add after:=Worksheets(Worksheets.Count)


With something like this line. Change the name of your "Template" sheet to suit.
VBA Code:
Sheets("Template").Copy After:=Sheets(Sheets.Count)
 
Last edited:
Upvote 0
I've been using this code but I need to edit the sheet names before printing for project tracking purposes.
Would it be possible to tweak this code to add other elements to the name?
for example: App1-20_RA
something along the lines of wsName & "-20_RA"?
 
Upvote 0
VBA Code:
Sub AddWs()
    Dim i As Long, wsName As String
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Do
        i = i + 1
        wsName = "App" & i & "-20_RA"
    Loop While WorksheetExists(wsName)
    ActiveSheet.Name = wsName
    ActiveSheet.Range("A1").Value = i
End Sub

Function WorksheetExists(wsName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Worksheets(wsName).Name = wsName
    On Error GoTo 0
End Function
 
Upvote 0
VBA Code:
Sub AddWs()
    Dim i As Long, wsName As String
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Do
        i = i + 1
        wsName = "App" & i & "-20_RA"
    Loop While WorksheetExists(wsName)
    ActiveSheet.Name = wsName
    ActiveSheet.Range("A1").Value = i
End Sub

Function WorksheetExists(wsName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Worksheets(wsName).Name = wsName
    On Error GoTo 0
End Function

Works wonders and I see what I was doing wrong! Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top