How to make the sheet index name same as the sheet name after inserting sheets?

[ucanalways]

When a new worksheet is added to a workbook then Sheet4 takes the
Sheet3's index name and becomes Sheet3(Sheet4) and so on. I am
referencing the new worksheets using Sheets(i).Select. I want Sheet4
to be selected but inserting worksheets makes Sheet3 as Sheet4. So, I
am stuck here

Please let me know How to make the sheet index name same as the sheet
name after inserting sheets? Thanks

 

[Guest]

The sheets always reference from the left hand side of the tabs when using
either:-

For each ws in worksheets

or

For i = 1 to worksheets.count

However, the worksheet CodeName does not change. The CodeName is the name to
the left and not in brackets you see in the project explorer in the VBA
Editor. Looks something like this:-

Sheet1(MySheet)
Sheet2(Sheet2)
Sheet3(Sheet3)

The name in brackets is the one that you can change. The other name remains
as is and you can use it to reference a specific worksheet like this:-

Sheet1.Range("A1") in lieu of Sheets("MySheet").Range("A1")

Irrespective of user changes to the worksheet name, the code name remains
constant.

I realise that you might still have a problem when trying to loop through
the worksheets but you can still test for the code name of a worksheet like
this example:-

For i = 1 To Worksheets.Count
If Worksheets(i).CodeName = "Sheet2" Then
MsgBox "Correct Sheet2"
Else
MsgBox "Not correct sheet"
End If
Next i

Not exactly the answer you would like but hope it helps anyway.

Regards,

OssieMac

 

[ucanalways]

The sheets always reference from the left hand side of the tabs when using
either:-

For each ws in worksheets

or

For i = 1 to worksheets.count

However, the worksheet CodeName does not change. The CodeName is the name to
the left and not in brackets you see in the project explorer in the VBA
Editor. Looks something like this:-

Sheet1(MySheet)
Sheet2(Sheet2)
Sheet3(Sheet3)

The name in brackets is the one that you can change. The other name remains
as is and you can use it to reference a specific worksheet like this:-

Sheet1.Range("A1") in lieu of Sheets("MySheet").Range("A1")

Irrespective of user changes to the worksheet name, the code name remains
constant.

I realise that you might still have a problem when trying to loop through
the worksheets but you can still test for the code name of a worksheet like
this example:-

For i = 1 To Worksheets.Count
If Worksheets(i).CodeName = "Sheet2" Then
MsgBox "Correct Sheet2"
Else
MsgBox "Not correct sheet"
End If
Next i

Not exactly the answer you would like but hope it helps anyway.

Regards,

OssieMac

OssieMac,

Ws.codename was the key here and I got that from your code. Also, I
understood your explanation. Thanks for the detailed clarification.

I am now using

Sub aa()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName = "Sheet4" Then
ws.Select
Range("a1").Value = 100
'Else
' MsgBox "Not correct sheet"
End If
Next ws
End Sub