Get sheet name from composite code name

[Giordano Bruno]

Is there a simple way of retrieving the worksheet name from the code name when the code name = "Sheet" & "1", "2", "3", etc. I can do it by looping through all worksheets, but there must be an easier way.
Thanks in advance for any suggestions.

 

[Logit]

.
Not certain I understand the question ?

 

[StephenCrump]

.
Not certain I understand the question ?

Nor me.

Sheet1.Name?

 

[My Aswer Is This]

You said:
simple way of retrieving the worksheet name

Retrieve it to where?

And why do you think looping is so difficult?

 

[Giordano Bruno]

Sorry everyone. I want to run some code on 9 imported worksheets which will automatically receive the code names Sheet1, Sheet2, etc. xCount is a variable which loops through the numbers 1 to 9. At each iteration, I want to know the sheet name of the sheets with code names "Sheet1", "Sheet2", etc.
There is nothing wrong with looping through all sheets if there is no other way, but if there is something easier, I'd prefer that.
Many thanks for your interest.

 

[StephenCrump]

With ThisWorkbook
    For i = 1 To 9
        MsgBox .Sheets(.VBProject.VBComponents("Sheet" & i).Properties("index")).Name
    Next i
End With

You'll need to tick "Trust Access to the VBA project object model" in Excel's Trust Settings.

 

[Giordano Bruno]

Many thanks Stephen. The only problem with that is that I'm doing this for someone who knows even less about Excel than me. He would freak out at ticking "Trust access...". Looks like looping though all sheets is going to be the only solution in the circumstances.
Your help is much appreciated.

 

[Rick Rothstein]

Does this macro do what you want (code name is first, sheet name is in parentheses)...

[table="width: 500"]
[tr]
	[td]Sub ShowSheetNamesForCodeNames()
  Dim WS As Worksheet, NameList As String
  For Each WS In Worksheets
    NameList = NameList & vbLf & WS.CodeName & " (" & WS.Name & ")"
  Next
  NameList = Mid(NameList, 2)
  MsgBox NameList
End Sub[/td]
[/tr]
[/table]

 

[Giordano Bruno]

Hi Rick,

Many thanks for the code. NIt doesn't do exactly what I want, but I can easily adapt it to identify code name "Sheet1" and get the sheet name and it is neater than the loop I'm currently using.
Much appreciated.

 

[My Aswer Is This]

Not sure why your referring to "code names"

This message box will give you a list of sheet names:

Sub Sheet_Names()
Dim ans As String
Dim i As Long
    For i = Sheets.Count To 1 Step -1
    ans = Sheets(i).Name & vbNewLine & ans
    Next
    MsgBox ThisWorkbook.Name & vbNewLine & "Sheet names are:" & vbNewLine & ans
    
End Sub