Rename Sheet by VBA

[nagovind]

Dear All,

We have several sheets in a workbook

Requirement is i will switchover to any sheets, Whichever is my current worksheet need to be RENAMED as "INDEX" is the query

So if i switch to say worksheet 3 then worksheet 3 will be renamed as INDEX after running a macro. If i move to say worksheet 10, then the current worksheet 10 has to be renamed as INDEX after running a macro and the previous worksheet 3 has to be changed to any name to make the current active sheet to have that same name.

Please do the needful

Regards
Govind

 

[Luke M]

So, lets say you run the macro, and "Sheet3" gets renamed to "Index". Then you change to Sheet10 and start to run macro. What should Sheet3 get renamed to (since you can't have two sheets named "Index")?

 

[nagovind]

Dear Like,

Thank you for your reply.

Sheet3 get renamed to anyname.

Shall we go with RND function? or any other way out with reasonable name?

Regards
Govind

 

[Luke M]

Something like this might work then:

Sub ChangeSheetName()
Dim newIndex As Worksheet
Dim oldIndex As Worksheet

Set newIndex = ActiveSheet

'Check for previous existing
On Error Resume Next
Set oldIndex = Worksheets("INDEX")
On Error GoTo 0

'Uses assumption that all code names are unique
If Not oldIndex Is Nothing Then
    oldIndex.Name = oldIndex.CodeName
End If
newIndex.Name = "INDEX"

'Continue on with rest of code
End Sub

 

[nagovind]

Dear Luke,

The code is perfect and solved the issue

In the mean time i have used the below code. But your code is professional and i'm using it now!

Public Sub RENAMEINDEX()

X = Rnd()
Sheets("INDEX").Name = "INDEX" & X
ActiveSheet.Name = "INDEX"

End Sub