Maybe something like this will get you closer:

Option Explicit
Sub testme()

Dim ActWkbk As Workbook
Dim ActCodeName As String
Dim RevCodeName As String
Dim NewSheet As Object

Set ActWkbk = ActiveWorkbook
ActCodeName = ActiveSheet.CodeName

If UCase(Left(ActCodeName, 1)) = UCase("A") _
And Len(ActCodeName) 3 Then
RevCodeName = Left(ActCodeName, Len(ActCodeName) - 3)
Set NewSheet = FindSheetByCodeName _
(wkbk:=ActWkbk, CDName:=RevCodeName)
If NewSheet Is Nothing Then
'beep 'just a beep
MsgBox "no parent(?)"
Else
ActWkbk.Activate 'just in case
NewSheet.Select
End If
End If

End Sub
Function FindSheetByCodeName(wkbk As Workbook, CDName As String) _
As Object 'any type of sheet

Dim iCtr As Long
Dim mySheet As Object

Set mySheet = Nothing
For iCtr = 1 To wkbk.Sheets.Count
If LCase(wkbk.Sheets(iCtr).CodeName) = LCase(CDName) Then
'found it
Set mySheet = wkbk.Sheets(iCtr)
Exit For 'stop looking
End If
Next iCtr

Set FindSheetByCodeName = mySheet

End Function


Kris_Wright_77 wrote:

I have a spreadsheet with multiple layers of sheets, where I have used the
Sheet Name to give a Description meaningful to other users, but have a
separate codename that uses a numbering system, which should allow a
button/macro to step up through the layers.

In an earlier version the Sheet Name was linked to the Codename, but I now
have too many layers & variations in Sheet names to manage easily.
Example of problem and Code below

Is anyone able to advise where I need to make some mods for it to work?

Thanks very much

Kris

Example Sheet Structure
Sheet Codename
Main A
Mgmt Processes A_01
Project Activites A_02
Mgmt Proc 1 A_01_01
Proj Act 1 A_02_01
Proj Act 2 A_02_02
Proj Act 2a A_02_02_01
Proj Act 2b A_02_02_02

Sub Go_Home()
'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _
Else Returns to Main Sheet
If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) =
"A" Then
ToSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3)
ToSheet.Select
Else
A.Select
End If
End Sub

The macro then allows user to return to Proj Activities from Proj Act 2,
regardless of ordering of sheets or Sheet Name.

Of Course the Sheet Naming isnt anything close to being as friendly as that
above, but hope it makes it easier to undertand

--

Dave Peterson