Use CodeNames to Reference Sheets in Excel Workbooks

Sometimes you need to create a macro that will work even if the sheet names that it references change.

If you have recorded a macro in Excel that references a specific sheet in your workbook, you know the code will continue to work only if the sheet name remains the same. For example, if your worksheet is named Taxap, and the code in your macro reads Sheets("Taxap").Select and then you change the worksheet name, the macro will no longer work. This is because the macro recorder generates code based on the sheet's tab name or on the name you see when working in Excel.

To overcome this limitation, you have two options, the first of which is to use index numbers. A sheet's index number is determined by its position in the workbook. The leftmost sheet will always have an index number of 1, the next worksheet immediately to the right will always have an index number of 2, and so on. Excel VBA enables you to specify any sheet by using its index number, but unfortunately Excel does not use this method when you record a macro.

Also, although using an index number such as Sheets(3).Select is a better option than using Sheets("Taxap").Select, the sheet's position in the workbook could change if you add, remove, or move sheets.

Instead of using index numbers, savvy VBA coders use CodeNames. Each sheet in a workbook is given a unique CodeName that does not change even when that sheet is moved or renamed, or when any other sheets are added. You can see a sheet's CodeName only by going into the VBE (press Alt/ Option-F11) and then displaying the Project window if necessary (select View → Project Explorer or press Ctrl-R).

The only time you cannot use a sheet's CodeName is when you reference a sheet that is in a workbook different from the one in which the code resides.