Sheet Code Names

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
I read that you can write VBA code using a sheet's code name rather than is sheet name or index... this way when someone changes the sheet name, or moves the sheet in the workbook the code still works.

So, I tried a couple of simple lines of code but am getting an error messgage.

Sheet3.Range("A1:B5").Select

returns an error of "Select Method of Range Class Failed"

However this code works:

Sheet3.Activate
ActiveSheet.Range("A1:B5").Select


I am wondering why? When I type the code the Auto List works... so I assume I am spelling things correctly??
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
How are you actually running this code?

Do you really need to select the range?

You don't actually have to do that to work with a range.
 
Upvote 0
I am not really using this code, just trying to see how using the sheets code name works rather than refering to sheets by their sheet name.

I do have some instances however that I have to select a range.
For instance, we use Essbase, and in order to retrieve data you first need to select the range (if there is data surrounding the essbase range).
In those cases I would launch the code by either a button or a WorkSheet_Activate Sub proceedure. When the user activates the sheet the essbase range is highlighted and all they have to do is click retrieve.
 
Upvote 0

Forum statistics

Threads
1,223,483
Messages
6,172,532
Members
452,463
Latest member
Debz

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
Back
Top