VBA & Macro Problems

Sort an Excel Worksheet Table of Contents Alphabetically with VBA

Olivia MillerOlivia Miller Nov 20, 2026 868 views

Question details

Automatically alphabetize worksheet names in a hyperlinked table of contents each time an Excel VBA macro recreates it.

Sort an Excel Worksheet Table of Contents Alphabetically with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the macro

In Excel, select Developer > Visual Basic, then open the module containing the macro that creates the table of contents.

2
Find the list range

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.

3
Insert the sort command

Immediately before End With, add: .Sort Key1:=.Offset(0, 1), Header:=xlYes

4
Run and check the macro

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.

Add a sort command to the existing table-of-contents macro
Match the command to your range: The offset points one column to the right of the With range, and Header:=xlYes treats the first row as a header. If your layout differs, adjust the sort range, key, or header setting to match it.
Free Microsoft Office alternative

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.

Open and edit spreadsheets with a familiar interface.Compatible with Microsoft Excel formats, including .xlsx and .xls files.Navigate worksheets and manage spreadsheet content in WPS Spreadsheets.Move everyday Office documents to a lightweight, free suite.
microsoft office alternative - wps office

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.