Sheet Names and Code Names

[mavsmoney]

I am trying to write a function that takes a cell's value, for example A1 = Sheet1, which is the codename of Sheet1, and return the worksheet name (which appears on the tab).

So in Microsoft Excel Objects you have Sheet1 (HelloWorld)

In cell A1 is the value Sheet1
In cell A2 I to write a function =GetSheetName(A1) and it returns the value HelloWorld.

I was able to do this in the reverse order (get Sheet1 from HelloWorld) with:

Public Function GetSheetName(rCell As String)
GetSheetName = rCell
GetSheetName = Worksheets(rCell).CodeName
End Function

But this does not work the other way around.

Thanks

 

[mavsmoney]

Not exactly a function, but I found a solution that will work in a subroutine.

Sub GetSheetName()
Dim Cell As Range
Dim WS As Worksheet
'Change Range in next line to the Range with Sheet1, Sheet2....'
For Each Cell In Worksheets("Example").Range("A1:A10")
For Each WS In ActiveWorkbook.Worksheets
If WS.CodeName = Cell.Value Then
Cell.Offset(0, 1).Value = WS.Name
End If
Next
Next
End Sub