Sort Array of sheets to front of workbook

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,779
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,329
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
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
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,779
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

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
Top