At a minimum I'd include

Dim TOSheet as excel.worksheet

Set TOSheet = ...
--
HTH,

Barb Reinhardt



"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