I have multiple worksheets, in my workbook, each renamed from original
"Sheet#" name. Is there a way to still reference each sheet in a macro as
Sheet1, Sheet2...., or can you only reference them by their current
caption-name?

Thanks,
Les

The best way is to reference a worksheet by its name because you can move the
order of the worksheets and then the macro will not work.

use either
worksheets("Sheet2")
or
worksheets(2)
or
sheets("Sheet2")
or
sheets(2)



"WLMPilot" wrote:

I have multiple worksheets, in my workbook, each renamed from original
"Sheet#" name. Is there a way to still reference each sheet in a macro as
Sheet1, Sheet2...., or can you only reference them by their current
caption-name?

Thanks,
Les

If you want your code to be bug proof you should not refer to sheets by their
tab name as tab names often get changed over time. Worksheets are objects and
can be referenced primarily using one of three ways. By index, by tab name or
by code name.

Index and tab name are generally not a good idea as they can change as
sheets are added or deleted or as tabs are renamed. When that happens code
either crashes or worse still operates on the wrong sheet.

Code name is really the best way to go. In the VBE the project explorer will
show you each sheet with something like Sheet1(MyTabName). Sheet1 is the code
name and MyTabName is the caption. You can directly reference the sheet in
code using Sheet1. So these two lines of code are roughly equivalent.

msgbox sheet1.name
or
msgbox worksheets("MyTabName").name

The difference is that Sheet1 refers directly to the worksheet object and is
defined at compile time. Thus you get an intellisence dropdown when you type
Sheet1. The other is only defined at run time so you do not get an
intellisence drop down.

Note that you can change the code name of the sheet by changing the (Name)
preperty in the properties window.

--
HTH...

Jim Thomlinson


"WLMPilot" wrote:

I have multiple worksheets, in my workbook, each renamed from original
"Sheet#" name. Is there a way to still reference each sheet in a macro as
Sheet1, Sheet2...., or can you only reference them by their current
caption-name?

Thanks,
Les

On Apr 8, 1:51*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
If you want your code to be bug proof you should not refer to sheets by their
tab name as tab names often get changed over time. Worksheets are objects and
can be referenced primarily using one of three ways. By index, by tab name or
by code name.

Index and tab name are generally not a good idea as they can change as
sheets are added or deleted or as tabs are renamed. When that happens code
either crashes or worse still operates on the wrong sheet.

Code name is really the best way to go. In the VBE the project explorer will
show you each sheet with something like Sheet1(MyTabName). Sheet1 is the code
name and MyTabName is the caption. You can directly reference the sheet in
code using Sheet1. So these two lines of code are roughly equivalent.

msgbox sheet1.name
or
msgbox worksheets("MyTabName").name

The difference is that Sheet1 refers directly to the worksheet object and is
defined at compile time. Thus you get an intellisence dropdown when you type
Sheet1. The other is only defined at run time so you do not get an
intellisence drop down.

Note that you can change the code name of the sheet by changing the (Name)
preperty in the properties window.

--
HTH...

Jim Thomlinson



"WLMPilot" wrote:
I have multiple worksheets, in my workbook, each renamed from original
"Sheet#" name. *Is there a way to still reference each sheet in a macro as
Sheet1, Sheet2...., or can you only reference them by their current
caption-name?

Thanks,
Les- Hide quoted text -

- Show quoted text -

To clarify, you change the name of the sheet in the properties
window ... once you're in VBE.

Chris