Sort Array of sheets to front of workbook

[ajm]

Folks I had to have excel reinstalled recently and in doing so, the techy deleted my personal macro workbook. My fault, I should have told him to save that folder. Anyway, I am rebuilding from memory at the moment. just ran into my first problem. I used to have a neat little macro that would look at all the sheets, and if a sheet was in an array, move that sheet before the first sheet. It excluded two sheets when running, one called "Index Sheet" and another called "Mater Outreach". these two sheets are always at the left most positions of the tabs. has anyone got something similar that essentially sorts an array of sheets to the front of the workbook.

 

[steve the fish]

Something like this perhaps?

arr = Array("Sheet4", "Sheet5")

a = 2 'ignore first two positions

For Each sh In ThisWorkbook.Worksheets
    If Not IsError(Application.Match(sh.Name, arr, 0)) Then
        sh.Move Before:=Sheets(a + 1)
        a = a + 1
    End If
Next

 

[Fluff]

Another option

Sub ajm()
   Dim Ary As Variant
   Dim i As Long, j As Long
   
   j = 3
   Ary = Array("Sheet1", "Sheet3", "Master")
   For i = 0 To UBound(Ary)
      If Evaluate("Isref('" & Ary(i) & "'!A1)") Then
         Sheets(Ary(i)).move Sheets(j)
         j = j + 1
      End If
   Next i
End Sub

 

[ajm]

Thank you both for your responses. They were sufficient to remind me what I had originally.

Sub SortArrayToFront()

Dim wSheet As Worksheet

 Dim ArrayList As Variant
 Dim x As Variant
  Dim l As Long
    l = 2
   
For Each wSheet In Worksheets
 ArrayList = Array("US", "TH", "PGK", "PCX", "NPB", "MWA", "MR1", _
                               "MGU", "MAP", "KF", "JM4", "JEP", "JC", _
                               "GLB", "AVS", "ANB") 'put your items here in desired order

    x = Application.Match(wSheet.Name, ArrayList, 0)
        If Not IsError(x) Then
        wSheet.Move Before:=Sheets(l + 1)
            l = l + 1
        End If
            
Next
End Sub

 

[Fluff]

Glad it's sorted & thanks for the feedback