Navigate Between Sheets Using Named Ranges

[Hmerman]

Hello,
Hope you are well.

I am trying to navigate between sheets using named ranges I set up. E.g. I have a Sheet1 named Polar and Sheet2 named Inventory. When I click on a home button on the Inventory sheet it should navigate to the Polar sheet using the named range "home" that is set up for cell Polar!A1.

I have written code but it does not work from the Inventory sheet (it does work in the Polar sheet):
<code>
<code>
Sub navigateSh()

'navigate to polar sheet using named range "home"
Range(Names("home")).Activate

End Sub
</code></code>

Can someone please help me to apply this idea to my code?

P.S. The reason I use named ranges is for in case the user changes the sheet names.

Regards
Herman

 

[My Aswer Is This]

You need to use what is called the Sheets "Code Name"
This name never changes even when you change the sheet name.

Right click on the sheets tab select View code.
Find the sheet in the panel on the left.

See you will see something like this:

Sheet4(Polar)

Sheet4 is the code name which never changes.

So then we can use a script like this:

Private Sub CommandButton2_Click()
Application.Goto Sheet4.Range("home")
End Sub

 

[My Aswer Is This]

Or if you do not want to use named range you could do it like this:

Private Sub CommandButton2_Click()
Application.Goto Sheet4.Range("A1")
End Sub

 

[Hmerman]

Clever. That is great!

Thank you very much.

I actually used the following from your code without the sheet code name and it still worked:
<code>
Sub navSh()

Application.Goto Range("home")

End Sub
</code>

 

[My Aswer Is This]

Glad I was able to help you. And glad you know how to modify scripts to your needs.

Clever. That is great!

Thank you very much.

I actually used the following from your code without the sheet code name and it still worked:
<code>
Sub navSh()

Application.Goto Range("home")

End Sub
</code>