Correct way reference to sheets in code

Kade

Active Member
Joined
Jul 20, 2002
Messages
258
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This selects the second worksheet in a workbook, regardless of its name. JSW

Worksheets(2).Select
 

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Feb 16, 2002
Messages
761
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
 

Forum statistics

Threads
1,144,741
Messages
5,726,014
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top