Correct way reference to sheets in code

[Kade]

When I record a macro that includes changing a worksheet it records the sheet by its TAB name.

However if I subsequently change the TAB name the macro does not find the sheet.

Is there a way to record the code so that it registers its "root"sheet number.

tia


kd

 

[Joe Was]

This selects the second worksheet in a workbook, regardless of its name. JSW

Worksheets(2).Select

 

[BigC]

On 2002-11-07 23:09, Joe Was wrote:
This selects the second worksheet in a workbook, regardless of its name. JSW

Worksheets(2).Select

Joe
What happens if the relative position of the sheet changes so that it is no longer the 2nd sheet - will the VBA code also change, or will the sheet still retain the ID of 2?

Tx

 

[Joe Was]

When using "Worksheets(#).Select

the "#" is the tab position from the Left to the right, starting with position "1" no matter where the sheets are moved!

So if you have three sheets, Sheet1, Sheet2 and Sheet3 and #=2 then you get Sheet2, but if you move sheet2 to the end. Then #=2 will give you: Sheet3. The new order being Sheet1, Sheet3 and Sheet2.

I do not know of an automatic property to address by number the re-named sheet if re-positioned. But, with the number system you can build into your macro a math function to re-address the sheet # based upon it's movements. You can also code the sheet name into a variable and then refer to that sheet by its variable. That way no mater what you do to it your macro will know what sheet the code is referencing! JSW

 

[RichardS]

Joe,

When you view code for a sheet, under the Microsft Excel objects, it lists the sheets in the Workbook. I renamed sheet1 to test, then dragged it to the end of the workbook. When I view code for that sheet, it is still listed as the first object, Sheet1 (test). If you delete it, and view code for Sheet2, there is no sheet1 object. I'm sure I've seen this coded sheet reference somewhere on this Board. There must be a way to refer to this constant?

Richard