Order Sheets by List

[pyrokidd89]

Hello,

I have a workbook where each sheet is a task. In cell B3 of each sheet I have set a priority for the task. For example B3=1 is my top priority. I would like to sort the sheets by priority. Also if two sheets have the same priority then sort those two alphabetically. I also must have a sheet named "Table of Contents" first and a sheet named "Template" second. Any help would be awesome.

 

[ranman256]

run : BuildToC

Sub BuildToC()
Dim sht As Worksheet, shtTarg As Worksheet
Dim vPrior As Integer


Sheets(1).Select
Sheets.Add
ActiveSheet.Name = "TOC"
Range("a1").Value = "Priority"
Range("b1").Value = "Sheet"


Range("B2").Select
Set shtTarg = ActiveSheet


For Each sht In Sheets
   sht.Activate
   vPrior = Range("B3").Value
   
   shtTarg.Activate
   ActiveCell.Offset(0, -1).Value = vPrior
   ActiveCell.Offset(0, 0).Value = sht.Name
   ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=sht.Name & "!A1", TextToDisplay:=sht.Name
   
   ActiveCell.Offset(1, 0).Select   'next cell
Next


SortToC
Set sht = Nothing
End Sub
Private Sub SortToC()
Dim r As Long


    Range("A2").Select
    r = ActiveSheet.UsedRange.Rows.Count
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("TOC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TOC").Sort.SortFields.Add Key:=Range("A2:A" & r), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("TOC").Sort.SortFields.Add Key:=Range("B2:B" & r), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TOC").Sort
        .SetRange Range("A1:B" & r)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

 

[pyrokidd89]

run : BuildToC

Thanks but not exactly what I was looking for. I have a script that creates a ToC already. I would like to move the actual sheets in the order of priority.

 

[pyrokidd89]

Nevermind I was able to figure it out with the use of some other scripts I found.

 

[SpillerBD]

John Walkenbach does that macro in his book as part of a teaching chapter.
I have "Excel 2013 Power Programming with VBA", but I sure it is still in the updated book for 2016.