Using worksheet (Name) eg Sheet1 instead of Name

[Sarah Mullette]

Hello, does anybody know whether in a macro you can refer to a sheet using its original name eg Sheet1 instead of the name given to it by the user?

(I have a macro which is used for more than one file and each time I use it I have to respecify my sheet names

 

[MosesOfOz]

I know that you can refer to a sheet through it's order...

if you want the leftmost sheet you can select it with:

Worksheets(1).Select

I would be interested if there is a way to do this, as my macro's tend to fall over if the sheet order is changed.

 

[Al Chara]

Try using the codename property. See the entry below which is from help files.

Note The value that you see in the cell to the right of (Name) in the Properties window is the code name of the selected object. At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.

Remarks

The code name for an object can be used in place of an expression that returns the object. For example, if the code name for worksheet one is "Sheet1", the following expressions are identical:

Worksheets(1).Range("a1")
Sheet1.Range("a1")
It’s possible for the sheet name to be different from the code name. When you create a sheet, the sheet name and code name are the same, but changing the sheet name doesn’t change the code name, and changing the code name (using the Properties window in the Visual Basic Editor) doesn’t change the sheet name.

_________________
Kind regards,<font size="5">^{<span style="text-decoration: overline">AL</span>}<u>_CHARA</u></font
This message was edited by Al Chara on 2002-08-12 18:51

 

[jag108]

I use a simple method of the actual name of the sheet i.e.
Worksheets("Actual name of sheet").Select

 

[Al Chara]

Let's say you have a worksheet named Sheet1.
You can use it in code in the following methods:
Worksheets("Sheet1").select
Sheets("Sheet1").select
Sheets1.select
But once a user changes the tab name of the sheet to something else, like Summary, only one of the top three methods will still be valide...Sheet1.select
The user only changed the sheet name not the sheet codename.

 

[A Nonomus]

yeah this would be very useful, as I have a macro that selects Sheet1 and then when you save then clean or clear the sheet for the next time. the sheet that gets created is sheet2 and the macro is still looking for sheet one.

 

[Marc L]

Hello, does anybody know whether in a macro you can refer to a sheet using its original name eg Sheet1 instead of the name given to it by the user?

Hello, as the worksheet's codename does not change when the worksheet is renamed so just replace in your code the worksheet reference by its codename​

like for example Sheet1.Range("A1") rather than Sheets("Sheets1").Range("A1") …​

​

Working directly with codenames works only in the workbook where is located the VBA procedure.​