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.

 

[Rick Rothstein]

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.

How about this function then? Just pass it the code name (as a text string) and it will return the sheet name for that code named sheet...

[table="width: 500"]
[tr]
	[td]Function GetSheetName(CodeName As String) As String
  Dim WS As Worksheet
  For Each WS In Worksheets
    If WS.CodeName = CodeName Then
      GetSheetName = WS.Name
      Exit Function
    End If
  Next
End Function[/td]
[/tr]
[/table]

 

[Giordano Bruno]

Hi MAIT. Thanks for the code. I'm referring to Code Names because I don't know that the Sheet Names in the workbook will always be as expected. I am getting the user to use "Move" to dump the worksheets into my workbook and then I want to work on them one by one. I don't know where the user will dump them and the only way I can identify them is by their new Code Names, Sheet1, etc. All of the sheets in my workbook have had their code names changed to something useful.

 

[StephenCrump]

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.

Just coming back to this comment, and a little curious ...

How are you importing the worksheets, and why can't you determine the worksheet name as each is imported, rather than the roundabout solution of matching worksheet names to the default codenames?

 

[Giordano Bruno]

Hi Stephen. I have to rely upon the user to import the sheets and I am asking him to use the Move command to dump them all at once. That will be the easiest for him.

 

[Rick Rothstein]

Hi Stephen. I have to rely upon the user to import the sheets and I am asking him to use the Move command to dump them all at once. That will be the easiest for him.

Was the new code (a function) that I posted in Message #11 useful to you?

 

[My Aswer Is This]

Try this:
It will only show a list of the sheets which have not been given names.

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

 

[StephenCrump]

Hi Stephen. I have to rely upon the user to import the sheets and I am asking him to use the Move command to dump them all at once. That will be the easiest for him.

So one alternative would be to simply loop once through all worksheets in the workbook, testing if ws.Codename Like "Sheet*".

 

[Giordano Bruno]

Hi Stephen. That's more or less what I am doing except that there are nine worksheets being imported. I had hoped to find a simple one line solution, but it does not appear to be possible.

 

[mikerickson]

If there are always nine sheets imported and they are always imported in the same order, then the Index property of the sheet can be used. You can probe the Index of the last sheet before the imported 9 and then use Sheets(baseIndex + n) to refer to the nth sheet.

 

[Giordano Bruno]

Hi Mike. Nice to hear from you. I had thought of doing that, but it does require the user to put the new worksheets at the end. I have absolutely no confidence that the user will always do that so the Index property will not be reliable. I'm trying to make it foolproof even though I know that the fools will always outwit me in the end.