Creating a catalog for a workbook with many worksheets is necessary. Here we have a quick method to create a catalog in an Excel worksheet.
In our previous article How to Create a Catalog in Your Excel via Hyperlinks, we have talked about creating a catalog by inserting hyperlinks in to a worksheet. Creating a catalog for a workbook with many worksheets can be very useful. But adding hyperlinks one by one manually can be time-consuming. Therefore, here we have a new method to quickly create such a catalog.
Create a Catalog
In the image below, there are many worksheets in the workbook.
And now you can create a catalog in this workbook.
- Right click one of the tabs in the ribbon.
- And then click the button “Customize the Ribbon” in the menu.
- In the “Excel Options” window, check the option “Developer” in the “Main Tabs” area.
- And then click “OK”.
- Now click the tab “Developer” in the ribbon.
- And then click the button “Visual Basic” in the toolbar.
Thus, you have opened the Visual Basic Editor. Actually, you can also press the shortcut key “Alt +F11” on the keyboard to open the editor quickly.
- Now right click in the VBA projector.
- And then move your cursor on the option “Insert”.
- In the sub menu, choose the option “Module”.
Therefore, you have inserted a new module into the project.
- Now copy the following VBA codes into the new module:
Sub CreateMenu()
Dim a As Integer
a = 1
‘add a new worksheet
Worksheets.Add Before:=Sheets(1)
Sheets(1).Name = “Menu”
Worksheets(“Menu”).Range(“A1”).Value = “Menu”
For a = 2 To ActiveWorkbook.Worksheets.Count
Worksheets(“Menu”).Range(“A” & a) = Sheets(a).Name
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets(“Menu”).Range(“A” & a), _
Address:=””, _
SubAddress:=”‘” & Sheets(a).Name & “‘!R1C1”, _
TextToDisplay:=Sheets(a).Name
Next
End Sub
In this code, we insert a new worksheet named “Menu” in the beginning. If there is already a worksheet named “Menu”, you can either change the VBA code or change the name of the existing worksheet name. Besides, we will input the list in the first column in the new worksheet. If you need to input the catalog in different range, you can also modify it according to your need.
- Now press the button “Run Sub” in the toolbar
In addition, you can also press the button “F5” on the key board.
- Now come back to the interface of worksheet.
There is a new worksheet named “Menu” in the workbook. And all the sheet names are listed in the worksheet with the corresponding hyperlinks. From this method, you can easily create a catalog in a quick time.
A Comparison between the Two Methods
In our previous article How to Create a Catalog in Your Excel via Hyperlinks, we add the catalog manually. And we will compare the difference of the two methods in the table below.
| Comparison | Create a Catalog Manually | Use VBA Procedure |
| Advantage | 1. Every Excel users can use this method easily.
2. If you have no knowledge about VBA, you can use this method. |
1. Through VBA procedures, you can finish this task with just one click.
2. You can also change the VBA codes to fulfill other requirements. |
| Disadvantage | 1. If there are many worksheets in the workbook, you will certainly spend a lot of time on inserting hyperlinks.
2. You need to choose hyperlinks one by one, and this will easily lead to mistakes. |
1. If you are not familiar with VBA codes, you will meet with problems when running the codes.
2. Using VBA codes will complicate the task. |
Deal with Unexpected Situations in Excel
If you use Excel frequently to finish your task, you will inevitably meet with some errors. Most of the time, you can quit Excel and then restart it. Thus, those small errors will disappear. However, some mistakes in Excel will lead to severe Excel corruption. And the result can be unimaginable. At this moment, you can repair xls data problem by our proficient tool. With the help of this effective tool, the only thing you need to do is waiting for the recovery process to finish.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc data damage and outlook repair software products. For more information visit www.datanumen.com
1