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(s) remain the same. For example, if your worksheet is named Budget, and the code in your macro reads Sheets("Budget").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("Budget").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 (select Tools » Macro » Visual Basic Editor, or press Alt/Option-F11) and then displaying the Project window if necessary (select View » Project Explorer or press Ctrl-R).
In the figure, the CodeName for the sheet with a tab name of Budget is Sheet3. A sheet's CodeName is always the name that appears outside the parentheses when you look in the Project Explorer. You can reference this sheet with VBA code in the workbook by using Sheet3.Select, as opposed to Sheets("Budget").Select or Sheets(3).Select.
Figure. CodeNames and sheet names in the VBE Project window
If your workbook is already full of VBA code, recorded or written, that does not use a CodeName, you can change the code at the project level (all code in all modules in the workbook) by selecting Edit » Replace... while in the VBE.
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.