VBA to Sort worksheets from a list

[eeder1]

Does any know if it is possible to sort numerous worksheets based off a list of cells that are the names of the worksheets within the same file?

For example my worksheet names are:

YTD Texas
YTD Florida
Period Texas
Period Florida

I can sort the sheets by alpha but it puts the two YTD worksheets together when I need the two Texas sheets side by side (I need this on a file that contains over 100 worksheets otherwise I would do it manually)
I was wondering if I could create my order of sheet name in another worksheet and reference that list through vba code? thanks for any insight

 

[Ed Acosta]

This will sort the Names of the sheets without a cell reference. It assumes your sheets are name as the example you provided the names as you show in your example, It will sort by the state then it will sort by the first name so in your example it should show the sheets as

Period Florida
YTD Florida
Period Texas
YTD Texas

If you prefer it to read a cell reference it can easily be modified


Sub SortWS()

SH = ActiveWorkbook.Sheets.Count

For I = 1 To SH
NM2 = Split(Worksheets(I).Name)
For T = I To SH
NM1 = Split(Worksheets(T).Name)
If NM1(1) < NM2(1) Then Worksheets(T).Move before:=Worksheets(I)
If NM1(1) = NM2(1) Then
If NM1(0) < NM2(0) Then Worksheets(T).Move before:=Worksheets(I)
End If
Next T
Next I

End Sub

 

[eeder1]

wow that is awesome..How could I modify the code to reference a list of say 100 worksheet names in another worksheet within another file (list.xls)? Many thanks

 

[Ed Acosta]

Ok Try this.

Sub SortWS()
' Assumes Source Listing is Already sorted
' If source Listing is not sorted additional coding will be
' needed to sort the source listing first
' There is no error checking so if the sheet name does not match the source list
' you will get an error if it attempts to move a sheet that doesnt exist

Dim ActiveWB As String
ActiveWB = ActiveWorkbook.Name                                                  'Capture Active Workbook Name
Dim SourceWB As Workbook
Dim SourceSH As String

Application.ScreenUpdating = False                                              'Turn ScreenUpdating OFf so its transparent

Set SourceWB = Workbooks.Open("C:\lists.xls", False, True)                      'Set the Source workbook Change the file Location
SourceSH = "Sheet1"                                                             'Set the Source Sheet Name

LastRow = SourceWB.Worksheets(SourceSH).Cells(Rows.Count, "A").End(xlUp).Row    'Determines Last Row based on column A if the names are a different column change A to appropriate column
ReDim SheetNames(LastRow)                                                       'Sets Array based on Number of Sheets
For T = 1 To LastRow
    SheetNames(T) = SourceWB.Worksheets(SourceSH).Cells(T, 1)                   'Read the sheet names in based on the Sourcesheet.  Assumes names are in Column A on source sheet Change the 1 to appropriate column
Next T
SourceWB.Close False                                                            ' close the source workbook without saving changes

Workbooks(ActiveWB).Activate                                                    'Make Sure workbook is active
Application.ScreenUpdating = True                                               'Turn Screen Updating on

For I = 1 To LastRow
For T = I To LastRow

If SheetNames(T) < Worksheets(I).Name Then Worksheets(SheetNames(T)).Move before:=Worksheets(I)
Next T
Next I

End Sub

 

[donh]

Help - - - if I wanted to change this code as noted to sort by a cell reference on each sheet - - - say R2 from the 4th worksheet to end - - - what would need changed?

 

[mszakalinski]

' you will get an error if it attempts to move a sheet that doesnt exist

This one works perfect but how to skip to next name when doesnt found on the lists.xls ? any hint for junior vba learner?

 

[donh]

Here is what I came up with going through the forum (so I will not take any credit for this) - - - I have 2 cells that I am using that this will look to so it does not look to a list - - - I just tried it out and if the cell is blank it puts that worksheet first. This will start from sheet 5 to end of the workbook

so here's to the blind leading the blind . . . hope this helps

Sub dSortSheetsByCell()
 
    With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationAutomatic
    End With

Dim sh As Worksheet, i As Long
Dim iAnswer As VbMsgBoxResult
 

   iAnswer = MsgBox("To sort sheets by Job Title Order   Click  YES" & vbNewLine & vbNewLine & _
     "To sort sheets by Employee Name Order   Click  NO" & vbNewLine & vbNewLine & _
     "To close this window without doing any sorting   Click  CANCEL     ", vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
    For i = 5 To Sheets.Count
    For j = i + 1 To Sheets.Count
    
     If iAnswer = vbYes Then
        If UCase$(Sheets(j).Range("Q2").Value) < UCase$(Sheets(i).Range("Q2").Value) Then _
            Sheets(j).Move Before:=Sheets(i)
    End If
    
    
       If iAnswer = vbNo Then
        If UCase$(Sheets(j).Range("Q1").Value) < UCase$(Sheets(i).Range("Q1").Value) Then _
            Sheets(j).Move Before:=Sheets(i)
    End If
 
    Next j
    Next i

    With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
    End With
     
End Sub

 

[mszakalinski]

Thanks donh but this does not make what i want to achieve, i just need probably 1-2 lines where blank SheetNames will be skipped to next and checked again, can anyone advise?