Help w/Macro - Using Sheet Template Need It to Auto-Rename

[excelguy23]

Hi all. Need some help with a macro I'm currently using. Before I get into that this is what it already does:

• Inserts an existing worksheet template and places it at the end (or after all) the existing worksheets.

What I would like the macro to also do is:

• Automatically rename the inserted worksheet (via my worksheet template) in sequence to the existing worksheets
  
  Example: So let's say I have 3 existing worksheets titled Sheet1, Sheet2, Sheet3 (that's basically the default naming Excel uses). But would when I insert a new worksheet (via my worksheet template) it is shown as Sheet. And if I insert another one, it would be Sheet (2).
  So basically it is showing as such:
  
  Sheet1, Sheet2, Sheet3, Sheet, Sheet (2)
  
  What I would like the macro to do is automatically rename those last 2 worksheets (and any subsequent worksheets thereafter) in sequential order. Basically displaying it as such:
  
  Sheet1, Sheet2, Sheet3, Sheet4, Sheet5...(and so on and so forth)

If anybody could help me out with this, I would greatly appreciate it.
Here is the Macro code I'm working with. Thank you in advance!!!

Sub Macro1()

Dim sh As Worksheet
Dim shName As String
Dim n As Integer

'Name of the sheet template
shName = "Sheet.xlt"

'Insert sheet template
With ThisWorkbook
Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _ After:=.Sheets(.Sheets.Count))
End With

End Sub
​

 

[Peter_SSs]

excelguy23

Welcome to the MrExcel board!

See if this does it.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><br><SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> shName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> OldName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, NewName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#007F00">'Name of the sheet template</SPAN><br>shName = "Sheet.xlt"<br><br><SPAN style="color:#007F00">'Work out new sheet name</SPAN><br>OldName = Sheets(Sheets.Count).Name<br>NewName = "Sheet" & Right(OldName, Len(OldName) - 5) + 1<br><br><SPAN style="color:#007F00">'Insert sheet template</SPAN><br><SPAN style="color:#00007F">With</SPAN> ThisWorkbook<br><SPAN style="color:#00007F">Set</SPAN> sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _<br>    After:=.Sheets(.Sheets.Count)).Name = NewName<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[excelguy23]

Peter just tried this out, unfortunately I'm getting a "run-time" error. Not sure what's going on, but thanks for assisting me with this.

excelguy23

Welcome to the MrExcel board!

See if this does it.


Sub Macro1()

Dim sh As Worksheet
Dim shName As String
Dim n As Integer
Dim OldName As String, NewName As String

'Name of the sheet template
shName = "Sheet.xlt"

'Work out new sheet name
OldName = Sheets(Sheets.Count).Name
NewName = "Sheet" & Right(OldName, Len(OldName) - 5) + 1

'Insert sheet template
With ThisWorkbook
Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
After:=.Sheets(.Sheets.Count)).Name = NewName
End With

End Sub

 

[Peter_SSs]

Peter just tried this out, unfortunately I'm getting a "run-time" error. Not sure what's going on, but thanks for assisting me with this.

When you get the error, are you able to "debug" and determine which line is causing the error?

Also, what is the run-time error?

When you ran the code and got the error, what was the name of the 'right-most' sheet before you ran the code?

 

[excelguy23]

Yes I get the debug error: Run-time error 13. Also points to this part of the code: "NewName = "Sheet" & Right(OldName, Len(OldName) - 5) + 1" as the error.

Also I should note that I currently have 5 sheets (listed from left to right): Info, Sheet1, Sheet2, Sheet3, Sheet4.

 

[Peter_SSs]

Yes I get the debug error: Run-time error 13. Also points to this part of the code: "NewName = "Sheet" & Right(OldName, Len(OldName) - 5) + 1" as the error.

Also I should note that I currently have 5 sheets (listed from left to right): Info, Sheet1, Sheet2, Sheet3, Sheet4.

Try adding the following code immediately before the "OldName = ..." line. What is the result when the code is then run?

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> Sheets(Sheets.Count)<br>    MsgBox "Name = " & .Name & vbLf & "Len = " & Len(.Name)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Also, what version of Excel are you using?

 

[excelguy23]

It's still not working. But you know what I decided to do instead? I decided to go at it the "hard way". Instead of inserting my existing sheet template, I recorded a new macro that inserted the default worksheet (thus continuing the numbering sequence) and recorded/built the form from the ground up; including all formats. Tough, but it works.

So just wanted to say thanks for attempting to help me out.