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

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




"MWS" wrote in message
...
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

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..

"MWS" wrote:

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

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



"TomHinkle" wrote in
message
...
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..

"MWS" wrote:

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

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 ...
--
HTH...

Jim Thomlinson


"MWS" wrote:

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

Thank You Very Much For Your Help!!!!

"Jim Thomlinson" wrote:

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 ...
--
HTH...

Jim Thomlinson


"MWS" wrote:

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