As I promised last week, today’s tutorial is a dead easy macro that will allow you to extract a list of your worksheet tab names.
You can then use this list to:
- Create an index of your worksheets. Why not add Hyperlinks so you can quickly navigate your workbook.
- Or use them to create references on the fly with the INDIRECT function and much more.
- Add a command button to the worksheet. Go to the Developer tab of the ribbon > Insert > Command Button from the ActiveX Controls group.
If you haven’t got the Developer Tab do the following:
- Excel 2007 > Windows Button > Excel Options > Popular > Show Developer tab in the ribbon > OK.
- Excel 2010 > File tab > Options > Customize Ribbon > on the right hand window (Customize the Ribbon) tick the Developer box > OK.
Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count With NewSheet.Cells(i, 1) .NumberFormat = "@" .Value = CStr(Sheets(i).Name) End With Next i
So it looks like this:
Now you’re ready to click the command button and watch the magic as Excel inserts a new sheet in your workbook with all of your sheet names listed and ready for action!
If you want to save the macro in your workbook you need to save the workbook as a 'Macro Enabled Workbook'.
To do this go File > Save As > from the 'Save as type' list select 'Excel Macro-Enabled Workbook (*.xlsm)'.