The following is a VBA function I have written to do just that:

Public Function ToSheetName(CodeName As String, quote As Integer) As String
'
' Will return the actual sheet name, given the original name (codename).
' There is also the option to have it quoted, or not, as some of the uses of
' the name may or may not expect it to be quoted.
'
Dim theSheets As Sheets
Dim aSheet As Variant
Set theSheets = Sheets
For Each aSheet In theSheets
If aSheet.CodeName = CodeName Then
If quote Then
ToSheetName = "'" & aSheet.name & "'"
Else
ToSheetName = aSheet.name
End If
Exit For
End If
Next aSheet
End Function



michael.beckinsale wrote:

How do l get the sheet name from the sheet code name?
30-Mar-10

I am sure this should be pretty simple but so far the answer has
eluded me.

I am doing a project which involves retrieving data about the vbe.
Thanks to Chip Pearson's excellent code examples so far it has gone
fine. However l am having trouble retrieving the worksheet name that
appears on the worksheet tabs. So using Chip's code l can retrieve the
sheet code names, Sheet1, Sheet2 etc and list them as required. Lets
say the the tab name that appears to the users is "A" for Sheet1 and
"B" for Sheet2

So lets say that l now have Sheet1 in Cell(1,1) of a sheet called
"MyModules". The question is how do l get the tab name ("A") as it
appears to the users?

Debug.Print Sheet1.Name returns "A"
Debug.Print Sheets(Sheet1.Name).Name returns "A"

but if you use code something along the lines of:

Sub ReturnTabName

Cells(1,1).Name
or
Sheets(Cells(1,1).Value).Name
or
Sheets("MyModules").cells(1,1).Name
etc

End Sub

nothing is returned!

All help gratefully appreciated

Regards

Michael

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Repeating Structures Table Looping and Table Extract
http://www.eggheadcafe.com/tutorials...g-structu.aspx