Navigate Between Sheets Using Named Ranges

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
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:

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
Or if you do not want to use named range you could do it like this:

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

Hmerman

Board Regular
Joined
Oct 2, 2016
Messages
102
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top