Sort Array of sheets to front of workbook

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Something like this perhaps?

Code:
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
 
Upvote 0
Another option
Code:
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
 
Upvote 0
Thank you both for your responses. They were sufficient to remind me what I had originally.

Code:
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
 
Upvote 0
Glad it's sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top