MAKING EXCEL WORK FOR YOU

Heading

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy


Referencing a Sheet in your code

It is important in VBA when working with multiple sheets to reference the sheet that you want your code to work with correctly. This lesson will help you understand how Excel recognises sheets in your workbook. For example

Range("A1".Value="Test" Or Cells(1,1).Value="Test"

The code above will only work on the ActiveSheet. To work with a specific sheet then the code must identify the correct sheet. This can be done in one of these ways:

Sheet Index Name:

This is the position of the Sheet in the Workbook, Excel determines the Index by numbering the Sheets from the left. So, to refer to the second sheet use Sheets(2).Cells(1,1).Value="Test". This is fine until someone moves the sheets around or adds sheets before the sheet you used, then the code will not have the expected results, it will write to the wrong sheet.

Sheet Tab Name:

This is the name that is manually entered into the Tabs of the WorkBook. By default Sheet1, Sheet2 etc. You should know that these can be altered by right clicking on the tab & choosing Rename. To use this method in your code Sheets("Main").Cells(1,1).Value="Test" However, this method too has a drawback, the user can change the sheet name as easily as you. If this happens the code will not work.

Sheet Code Name:

Each Sheet in a Workbook is given a unique CodeName that does not change even when that sheet is moved, renamed or other sheets are added. Each sheets Code Name can only be seen by going into the Visual Basic Editor (Tools->Macro->Visual Basic Editor or Alt+F11) and then displaying the Project Explorer (View->Project Explorer or Ctl+R)

In the screenshot opposite the Sheet named Main has a code name of Sheet2, that is the name outside the parentheses (brackets). This name cannot be changed by the user as the above ways can. So this is the generally accepted method used by experienced "coders". So, changing the code to

Sheet2.Cells(1,1).Value="Test"

This code will now refer to the sheet named "Main".

Tip:

Why not take it one step further? The Sheet Code Name can be changed in the VB Editor whilst designing the workbook like any other object such as a UserForm. You select the Name Property in the Properties Window & change it to something meaningful to it's use, like in this case MAIN or INVOICE. Then refer to it in your code like this INVOICE.Cells(1,1)

Referencing a Sheet in Excel VBA