• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

7. Macro Hacks > 83. Use CodeName to Reference Sheets in Excel Workbooks

Use CodeName to Reference Sheets in Excel Workbooks

Sometimes you need to create a macro that will work even if the sheet names 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(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.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint