Referencing sheet in excel

Question

I am new to excel vba and I have some questions regarding referencing a worksheet

I noticed that when I used

 Worksheets(3)

The worksheet would be obtained according to the sequence of the worksheet in the workbook

When I used

Worksheets("Name")

It would be retrieved according to the name of the worksheet

However, I found that both approach is troublesome because for method 1, I need to fix the sequence of the worksheet. Once I dragged the worksheet around, the reference would become incorrect.

Method 2 would need me to fix the work sheet name , which is not that flexible.

I noticed that at the left panel of VBA editor, under the Microsoft Excel Objects, whenever the worksheet is created, a new sheet like Sheet1 (Name) would be created.

Is there any way that I could reference the worksheet based the the Sheet1 variable, which I could fixed it so that I could freely drag the sheet around or change the worksheet name?

Thanks.

Chris has already answered your question. however, just to let you know, I answered a similar question few days ago. Have a look at this thread. stackoverflow.com/questions/9923439/… — Siddharth Rout

chris neilsen chris neilsen · Accepted Answer · 2012-04-02T06:50:29

The name you refer to is called the CodeName. You can refer to a sheet by this name.

Eg, for your example Sheet1 (Name) can be referenced as

Worksheets("name")

or

Sheet1

Eg Worksheets("name").Activate or Sheet1.Active would both work

Note that you can change this name to something meaningful in the Properties window of the VBA IDE, but you can't change it at run time

Referencing sheet in excel

1 Answers