Macro coding
 

Is there a way for coding in a macro to update if, for example, a worksheet
that it refers to is renamed? Here is a portion of a macro I use:
Workbooks.Open Filename:="U:\NTHEA\2009-10\OPER INVEST\BAYERI.xls",
Notify:=False
Sheets("bal").Activate

In this case, the "bal" sometimes gets renamed to something else, by another
user. That is, the other user changes or renames their worksheet and does
not tell me. Therefore my macro does not work.

So, is there a way for that name "bal" to change automaticallly if/when the
other user changes the name?

Thank you

Macro coding
 

What you need to do is use the Worksheet code name

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

Code named get a bit tricky if you aren't referencing them in the active
workbook. If you have questions about that, come back.

HTH,
Barb Reinhardt

"Flipper" wrote:

Is there a way for coding in a macro to update if, for example, a worksheet
that it refers to is renamed? Here is a portion of a macro I use:
Workbooks.Open Filename:="U:\NTHEA\2009-10\OPER INVEST\BAYERI.xls",
Notify:=False
Sheets("bal").Activate

In this case, the "bal" sometimes gets renamed to something else, by another
user. That is, the other user changes or renames their worksheet and does
not tell me. Therefore my macro does not work.

So, is there a way for that name "bal" to change automaticallly if/when the
other user changes the name?

Thank you

Macro coding
 

Use the sheet code or sheet index

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Flipper" wrote in message
...
Is there a way for coding in a macro to update if, for example, a
worksheet
that it refers to is renamed? Here is a portion of a macro I use:
Workbooks.Open Filename:="U:\NTHEA\2009-10\OPER INVEST\BAYERI.xls",
Notify:=False
Sheets("bal").Activate

In this case, the "bal" sometimes gets renamed to something else, by
another
user. That is, the other user changes or renames their worksheet and does
not tell me. Therefore my macro does not work.

So, is there a way for that name "bal" to change automaticallly if/when
the
other user changes the name?

Thank you

Macro coding
 

Is there some other way you can identify the sheet (eg. by its content?)

Tim


"Flipper" wrote in message
...
Is there a way for coding in a macro to update if, for example, a
worksheet
that it refers to is renamed? Here is a portion of a macro I use:
Workbooks.Open Filename:="U:\NTHEA\2009-10\OPER INVEST\BAYERI.xls",
Notify:=False
Sheets("bal").Activate

In this case, the "bal" sometimes gets renamed to something else, by
another
user. That is, the other user changes or renames their worksheet and does
not tell me. Therefore my macro does not work.

So, is there a way for that name "bal" to change automaticallly if/when
the
other user changes the name?

Thank you