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



Use one or both of these simple Microsoft Excel macros to list all the sheets in an Excel workbook.

Image: Aajan Getty Images / iStockphoto

Whether your Microsoft Excel workbook has three or 50 sheets, it’s important to know what you have. Some complex applications require detailed documentation that includes the names of the sheets. For those times when a quick glance isn’t enough and you need to have a list, you have several options: Or you can run a Quick VBA procedure. In this article, I’ll show you 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 is automated and easy to modify to meet individual needs.

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

I am 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 for success.

How to enter and run code in VBA

If you are new to VBA code, you might be wondering 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 instructions that are preformed when called. Some developers call a sub-procedure a macro and a function procedure a procedure because a procedure can take arguments. Many use the term macro for everything. Some, like me, tend to use the term procedure for everything. Additionally, Access has a separate macro function 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 the 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 macros. You can also import the downloadable .cls file that contains 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 web page. Instead, enter it manually or copy it into a text editor, then paste that 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 the procedure from the resulting dialog box shown in Figure A, and then click Run.

Figure A


How to run a macro from the Developer tab.

The bare VBA code

A simple list of sheet names is easy to generate using VBA with the Worksheets collection. List A shows a simple For Each loop that iterates 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

Sublist sheet names ()

‘List all sheet names in column A of Sheet1.

‘Updated to change the location of the list.

Sheets (“Sheet1”). Activate

ActiveSheet.Cells (1, 1) .Select

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

For each sh in the worksheets

ActiveCell = sh.Name

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


End of the submarine

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

This code will list both hidden and very hidden leaves, which you might 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 selection statements. To learn more about implicit and explicit references, read Excel Tips: How to Efficiently Select Cells and Ranges Using VBA.

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

How to generate hyperlink list 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, shown in List B, is similar to List A, but this code uses the Name property to create a hyperlink.

List B

Sub ListSheetNamesAsHyperlinks ()

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

Sheets (“Sheet1”). Activate

ActiveSheet.Cells (1, 1) .Select

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

For each sh in the worksheets

ActiveSheet.Hyperlinks.Add Anchor: = Selection, _

Address: = “”, Sub-address: = “‘” & sh.Name & “‘” & “! A1”, _

TextToDisplay: = sh.Name

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


End of the submarine

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

The Hyperlinks.Add property in the 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 address of the hyperlink.
  • SubAddress: The subaddress of the hyperlink
  • Tooltip: Information displayed when the mouse pointer stops over the hyperlink.
  • TextToDisplay: The text of the hyperlink.

The SubAddress argument of our procedure

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

constructs a string using the name of the current sheet and cell reference A1. For example, if the current sheet is Sheet1, this is equivalent to ‘Sheet1’! A1. Subsequently, when you click this hyperlink, it will take 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 modified to change the position of the list or to add more information to the simple list.

Also look


Leave A Reply

Your email address will not be published.