GoTo Reference

[Guest]

Hello, Can someone tell me if it's possible to refer to a worksheet
generically, when using the GoTo function, so if the worksheet name changes,
the code will still execute?

For instance, I have the following:
Application.Goto Application.Worksheets("Sheet1").Range("A154")

If the the Sheet1 name changes, the process halts. Can Sheet1 be reference
in a manner that regardless of the text within the worksheet label, the code
will still take the user to range("A154")?

Any and All Help is Appreciated - Thanks

 

[Chip Pearson]

You can use the codename rather than the worksheet name

Application.Goto Sheet1.Range("A10"), True

The codename of the sheet is listed in the project explorer (in
the VBA Editor, CTRL+R to display). The code name will not change
even if the worksheet name changes.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

you can use 'ordinal' values to refer to worksheets..

That is:
Instead of
Application.Goto Application.Worksheets("Sheet1").Range.....
you can write
Application.Goto Application.Worksheets(0).range

**** You can use either the sheet label, or it's ordinal position(number) in
the parenthesis.. **** BUT the number can be unpredictable.. Typically
"Sheet1" is always the first worksheet in the workbook (** the first sheet is
sheet Zero, etc)

I use this method when processing exports that name the sheet different
things..

 

[Chip Pearson]

Application.Goto Application.Worksheets(0).range

The Worksheets collection index is 1-based, so the first sheet is
Worksheets(1) not Worksheets(0).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message

 

[Guest]

Every sheet has a tab name and a code name. By refering to the code name you
eliminate the problem of changes made to the tab name. Try this...

Sheet1.Select
Sheet1.Range("A154").Select

You can change the Code name of the Sheet in the properties window of the VB
editor. The code name is the top item in the properties list. You can change
Sheet1 to something a little more descriptive like shtIncomeStatement ...

 

[Guest]

Thank You Very Much For Your Help!!!!

Every sheet has a tab name and a code name. By refering to the code name you
eliminate the problem of changes made to the tab name. Try this...

Sheet1.Select
Sheet1.Range("A154").Select

You can change the Code name of the Sheet in the properties window of the VB
editor. The code name is the top item in the properties list. You can change
Sheet1 to something a little more descriptive like shtIncomeStatement ...