VBA & Macro Problems

Sort Excel Worksheet Tabs by Values in a Main Sheet

John WilsonJohn Wilson Nov 20, 2026 868 views

Question details

Arrange approximately 200 Excel worksheet tabs according to stock numbers listed on a MAIN worksheet.

Sort Excel Worksheet Tabs by Values in a Main Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has stock numbers in column B of MAIN and corresponding worksheets that should follow the same order.
Observed behavior
The desired result is for matching worksheet tabs to appear immediately after MAIN in the order listed in column B.
Before you start

Check that MAIN!B2 and the cells below contain stock numbers matching the worksheet names, including any leading zeros. Save a copy as an Excel Macro-Enabled Workbook (.xlsm) and ensure the workbook structure is not protected.

Solution 1Recommended

Use VBA to order worksheet tabs from the MAIN list

Run a macro that moves matching sheets after MAIN in the order specified by column B.

The macro reads the list from bottom to top and moves each matching sheet immediately after MAIN. Unmatched names are skipped; sheets not listed remain outside the ordered group. Each listed worksheet name should appear only once.

1
Open the VBA editor

In desktop Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the sorting macro

Paste this code into the module: Sub SortTabsFromMain() Dim mainSheet As Worksheet Dim targetSheet As Worksheet Dim lastRow As Long Dim i As Long Dim sheetName As String Set mainSheet = ThisWorkbook.Worksheets("MAIN") lastRow = mainSheet.Cells(mainSheet.Rows.Count, "B").End(xlUp).Row If lastRow < 2 Then Exit Sub For i = lastRow To 2 Step -1 sheetName = CStr(mainSheet.Cells(i, "B").Value) If Len(sheetName) > 0 Then Set targetSheet = Nothing On Error Resume Next Set targetSheet = ThisWorkbook.Worksheets(sheetName) On Error GoTo 0 If Not targetSheet Is Nothing Then If targetSheet.Name <> mainSheet.Name Then targetSheet.Move After:=mainSheet End If End If End If Next i End Sub

3
Run the macro

Save the workbook as .xlsm, return to Excel, and select Developer > Macros. Choose SortTabsFromMain and select Run, then check the tab order after MAIN.

4
Optionally run it on opening

In the VBA editor, double-click ThisWorkbook in the project pane and add the following event procedure. Save the workbook; it will run when the workbook opens if macros are enabled: Private Sub Workbook_Open() SortTabsFromMain End Sub

Use VBA to order worksheet tabs from the MAIN list
Enable trusted macros only: Excel may block macros until you enable content. Enable macros only for workbooks and code you trust.
Free Microsoft Office alternative

Work with Stock Number Lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for working with stock lists and worksheet tabs. Its Microsoft Excel format compatibility makes it easy to move everyday spreadsheet work between applications; the Excel VBA automation described above may not be supported in every WPS edition.

Review and edit stock number lists in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for an easier transition from ExcelAccess a free, lightweight office suite
QA img-9

Frequently Asked Questions

What happens if a stock number has no matching worksheet?

The macro skips that entry and continues through MAIN column B. Check the stock number and worksheet tab name if a sheet does not move.

Why does the macro read the list from the bottom up?

Each matching sheet is moved immediately after MAIN. Processing MAIN!B2 and below in reverse leaves the tabs in the same top-to-bottom order as the list.

Can the tabs be sorted automatically when I open the workbook?

Yes. Add a Workbook_Open event in ThisWorkbook that calls SortTabsFromMain, save the file as .xlsm, and enable macros when opening a trusted workbook.

Will leading zeros in stock numbers affect matching?

Yes. The value read from column B must match the worksheet name. Store stock numbers with leading zeros as text in MAIN so the zeros are retained.

Why can't the macro move the worksheet tabs?

Check Review > Protect Workbook in Excel. If the workbook structure is protected, unprotect it before running the macro.