Change active sheet name

QUINABA

Board Regular
Joined
Jul 18, 2002
Messages
127
Good morning fellas!

How can I change the code below

Sub ChngSheetName()

ActiveSheet.Name = "Template1"

End Sub

So that if "Template1" already exist in the workbook it will rename to "Template2" and if that exist to "Template3" and so on.

Thanks :biggrin:
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
This should work:

Sub NameSheet()
Dim NameDone As Boolean, ThisOneFound As Boolean
Dim Suffix As Integer, i As Integer
NameDone = False
For Suffix = 1 To 100
If NameDone Then Exit Sub
ThisOneFound = False
For i = 1 To ActiveWorkbook.Sheets.Count
If Sheets(i).Name = ("Template" & Suffix) Then
ThisOneFound = True
i = ActiveWorkbook.Sheets.Count
End If
Next i
If Not ThisOneFound Then
ActiveSheet.Name = "Template" & Suffix
NameDone = True
End If
Next Suffix
End Sub
 
Upvote 0
That will name active sheet with Template & first free number

so if you have Template1,Template2,Template3
it will call the active one Template4

when you have Template1,Template3,Template4
it will call the active one Template2


Sub RenameSheet()

Dim i As Integer

On Error GoTo Error_Handler

i = 1

Start:

ActiveSheet.Name = "Template" & i

Exit Sub

Error_Handler:
i = i + 1
Resume Start

End Sub



I hope it will help you
 
Upvote 0
Thanks Earlyd!

Your code gives me this error message: (I already have Template1,2,3 and I'm running the code where the active sheet name is Template4)

Run time error '1004':

Cannot rename a sheet to the same name a another sheet, a referenced object library, or a workbook referenced by visual basic.

Any ideas?
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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