Sort Excel Worksheet Tabs by Values in a Main Sheet
Question details
Arrange approximately 200 Excel worksheet tabs according to stock numbers listed on a MAIN worksheet.

- 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.
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.
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.
In desktop Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.
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
Save the workbook as .xlsm, return to Excel, and select Developer > Macros. Choose SortTabsFromMain and select Run, then check the tab order after MAIN.
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

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.

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.




