How to use VBA procedures to generate a list of sheet names in an Excel workbook


Image: Aajan Getty Images/iStockphoto

Whether your Microsoft Excel workbook has three sheets or 50, it’s important to know what you have. Some complex applications require detailed documentation including sheet names. For those times when a quick glance isn’t enough and you need to have a list, you have several options: You can go the manual route, which would be tedious and require updating every time you make a change. Or you can run a quick VBA procedure. In this article, I will show you an easily editable code that generates a list of sheet names and hyperlinks. There are other ways to generate a list, but I prefer the VBA method because it’s automated and easy to modify to suit individual needs.

SEE: 69 Excel Tricks Every User Should Master (TechRepublic)

I’m using Microsoft 365 on a 64-bit Windows 10 system, but you can use older versions. To save time, download the .xlsx, .xls, and .cls files. Macros are not supported by the online version. This article assumes that you have basic Excel skills and are familiar with VBA, but even a beginner should be able to follow the instructions to be successful.

How to Enter and Run Code in VBA

If you’re new to VBA code, you might wonder about the terms procedure and macro. You will see them used interchangeably when VBA is the language used. This is not true for all languages. In VBA, a procedure and a macro are a named set of statements that are executed when called. Some developers call a subprocedure a macro and a function procedure a procedure because a procedure can accept arguments. Many use the term macro for everything. Some, like me, tend to use the term procedure for everything. Additionally, Access has macro functionality separate from any VBA code. Don’t get too hung up on the terms.

SEE: Windows 10: Lists of voice commands for speech recognition and dictation (free PDF) (TechRepublic)

To enter VBA code, press Alt+F11 to open the Visual Basic Editor. In the Project Explorer on the left, choose ThisWorkbook and enter the code. If you are using a ribbon version, you must save the file as a macro-enabled file to use the macros. You can also import the downloadable .cls file containing the code. Or, you can work with one of the downloadable Excel workbooks. If you enter the code yourself, please do not copy it from this webpage. Instead, enter it manually or copy it into a text editor, then paste this code into a module.

In the VBE, press F5 to run a procedure, but be sure to click inside the procedure you want to run. In an Excel sheet, click the Developer tab, click Macros in the Code group, choose Procedure in the resulting dialog shown in Figure Aand then click Run.

Figure A

The stripped down VBA code

A simple list of sheet names is easy to generate using VBA thanks to the Worksheets collection. List A shows a simple For Each loop that loops through this collection. For each sheet, the code uses the Name property to enter that name in column A, starting with A1, in Sheet1.

List A

Sub ListSheetNames()

‘List all sheet names in column A of Sheet1.

‘Update to change listing location.

Sheets (“Sheet1”).Enable

ActiveSheet.Cells(1, 1).Select

‘Generate a list of hyperlinks to each sheet in the workbook in Sheet1.

For each sh in worksheets

ActiveCell = sh.Name

ActiveCell.Offset(1, 0).Select ‘Move down one line.


End caption

When adapting this code, you may want to change the location of the list; do so by modifying the first two lines accordingly. The For Each loop also provides possibilities for modification. You can add header names or values ​​to number sheet names.

This code will list hidden and very hidden sheets, which you may not want. When this is the case, you will need to check the Visible xlSheetVisible and xlSheetVeryHidden properties. Also, since we are actively selecting A1 on Sheet1, the cursor moves to that location. If you don’t want the active cell to change, use implicit select statements. To learn more about implicit and explicit references, read Excel Tips: How to Select Cells and Ranges Efficiently Using VBA.

There are many changes you might want to make. For example, instead of a regular text list, you may want a list of hyperlinks.

How to Generate a List of Hyperlinks in Excel

It is not uncommon for a complex workbook to include a list of hyperlinks to each sheet in the workbook. The procedure you will use, illustrated in List Bis similar to List Abut this code uses the Name property to create a hyperlink.

List B


‘Generate a list of hyperlinks to each sheet in the workbook in Sheet1, A1.

Sheets (“Sheet1”).Enable

ActiveSheet.Cells(1, 1).Select

‘Generate a list of hyperlinks to each sheet in the workbook in Sheet1.

For each sh in worksheets

ActiveSheet.Hyperlinks.Add Anchor :=Selection, _

Address: =””, Subaddress: = “‘” & sh.Name & “‘” & ” !A1″, _

TextToDisplay :=sh.Name

ActiveCell.Offset(1, 0).Select ‘Move down one row


End caption

The first two rows select cell A1 in Sheet1. Update these two statements to move the list. The For Each loop loops through all the sheets, using the Name property to create a hyperlink for each sheet.

The Hyperlinks.Add property in For Each uses the form

.Add an anchor, an address, [SubAddress], [ScreenTip], [TextToDisplay]

Parameter information is listed below:

  • Anchor: a Range or Shape object.
  • Address: The hyperlink address.
  • SubAddress: the subaddress of the hyperlink
  • Tooltip: Information displayed when the mouse pointer rests on the hyperlink.
  • TextToDisplay: the text of the hyperlink.

The SubAddress argument of our procedure

Subaddress:=”‘” & sh.Name & “‘” & “!A1”

constructs a string using the current sheet name and cell reference A1. For example, if the current sheet is Sheet1, it gives ‘Sheet1’!A1. Subsequently, when you click on this hyperlink, it takes you to cell A1 on Sheet1. As before, you can easily modify this procedure to reflect how you want to use this list.

Both sub-procedures are easy to use. Both can be easily edited to change the position of the list or to add more information to the simple list.


About Author

Comments are closed.