Sort an Excel Worksheet Table of Contents Alphabetically with VBA
Question details
Automatically alphabetize worksheet names in a hyperlinked table of contents each time an Excel VBA macro recreates it.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro generates a table of contents with hyperlinks to worksheet tabs.
- Observed behavior
- Manual sorting is lost when the macro recreates the table of contents.
Open the workbook containing the table-of-contents macro and locate the VBA With block that defines the list to sort. Check that the list has a header and identify the column containing worksheet names; only enable macros from a trusted source.
Add a sort command to the existing table-of-contents macro
Use this approach when the existing With block defines the table-of-contents range and its offset identifies the worksheet-name sort key.
In Excel, select Developer > Visual Basic, then open the module containing the macro that creates the table of contents.
Locate the With block that writes the table of contents. Confirm that its range and the offset sort key are appropriate for the generated list, and that the sort range includes all columns that must stay together.
Immediately before End With, add: .Sort Key1:=.Offset(0, 1), Header:=xlYes
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), then use Developer > Macros to run the macro. Check that the names are alphabetical and the hyperlinks still point to the correct sheets.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and support for Excel formats, making it easier to move spreadsheet work between suites. It does not modify or fix a VBA macro in Microsoft Excel.

Frequently Asked Questions
Does this command sort the worksheet tabs themselves?
No. It sorts the table-of-contents range, not the order of the worksheet tabs. Reordering tabs requires separate VBA code.
Why does my manual sort disappear when I run the macro?
The macro recreates the list, replacing the manually sorted entries. Adding the sort command after the list is generated applies the order on each run.
What does Header:=xlYes do?
It tells Excel that the first row of the sort range contains headers. If your list has no header, use the appropriate header setting for your range instead.
What should I check if the sort command fails?
In Developer > Visual Basic, check the With range and confirm that .Offset(0, 1) identifies a valid worksheet-name sort key for your layout. Include all related table columns in the sort range so their entries stay together.




