VBA - Sort Worksheets in same order as list

[imcfcl]

Morning Guys,

I should probably credit you guys for most of the clever stuff behind a project I'm working on...

I have the below code that sorts a list of Doors that I have in row C17 downwards. Door 54, Door 7, Door 109 etc. The list is feeding a drop down box, so I needed this to help people find the door they were looking for, in the said drop down box.

Sub ListSorter()
    Dim LastRow As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).row
    Range("F17").Copy Range("D17")
    Application.CutCopyMode = False
    Range("D17").AutoFill Destination:=Range("D17:D" & LastRow)
    ActiveWorkbook.Worksheets("Home").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Home").Sort.SortFields.Add Key:=Range("D17:D" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Home").Sort
        .SetRange Range("C17:D" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       Range("D17:D" & LastRow).Select
       Selection.ClearContents
End Sub

I wondered if there was some code I could add to the end of this, that would the sort worksheets, which are all named after each cell in the list, in the same order.

 

[Peter_SSs]

Try this in a copy of your workbook. Add the blue bits to your existing code.

Sub ListSorter()
    Dim LastRow As Long
    Dim c As Range
    

    'The rest of your existing code here


    Application.ScreenUpdating = False
    On Error Resume Next
    For Each c In Worksheets("Home").Range("C17:C" & LastRow)
        Sheets(c.Value).Move After:=Sheets(Sheets.Count)
    Next c
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[imcfcl]

So happy with the result, It worked with two exceptions, those being sheets called Door 123, and Door 77A, but I'll add a bit of code to move them after Door 122 and Door 77

Thank you so much - again

 

[Peter_SSs]

It worked with two exceptions, those being sheets called Door 123, and Door 77A,

Was that my code not putting them in the order they appeared in C17:C & LastRow or your code not sorting them in the way you wanted?