How to sort a selection of sheets in a workbook

[renevromzicafe]

Hi! Sorting all sheets alphabetically in an workbook is not difficult. However, I would like to sort alphabetically only those sheets in which Range("B1") has value "RVC" and then move the sorted sheets to the end of the workbook.
Does anybody know how? Thanks!

 

[jolivanes]

Using an array is not necessary. Sheet names can go direct into a sheet.

Sub Maybe()
Dim shts, cs As String, ws As Worksheet, rng As Range, i As Long, j As Long
cs = ActiveSheet.Name
Application.ScreenUpdating = False
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Sheets(i).Cells(1, 2).Value = "RVC" Then shts = shts & "," & Sheets(i).Name
    Next i
shts = Split(Mid(shts, 2), ",")
Set ws = ThisWorkbook.Worksheets.Add
Set rng = ws.Cells(1, 1).Resize(UBound(shts) + 1)
rng = Application.Transpose(shts)
rng.Sort key1:=rng, order1:=xlAscending, MatchCase:=False
    For j = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets(ws.Cells(j, 1).Value).Move after:=Worksheets(Worksheets.Count)
    Next j
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Worksheets(cs).Select
Application.ScreenUpdating = True
End Sub

 

[Skyybot]

Here is an option using an Array. As always, test on a copy of your Workbook as unexpected results may occur.

Sub Move_Sheets()
Dim wb As Workbook, sht, shts(), cnt As Long, shtName() As String
Set wb = ThisWorkbook
For Each sht In wb.Sheets
    If sht.Range("B1").Value = "RVC" Then
        ReDim Preserve shts(cnt)
        Set shts(cnt) = sht
        cnt = cnt + 1
    End If
Next sht
cnt = 0
ReDim shtName(UBound(shts))
For Each sht In shts
    shtName(cnt) = sht.Name
    cnt = cnt + 1
Next sht
shtName = SortArrayAtoZ(shtName)
For Each sht In shtName
    wb.Sheets(sht).Move After:=wb.Sheets(wb.Sheets.Count)
Next sht
End Sub
Function SortArrayAtoZ(myArray As Variant)

Dim i As Long
Dim j As Long
Dim Temp

'Sort the Array A-Z
For i = LBound(myArray) To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) > UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

SortArrayAtoZ = myArray

End Function

 

[renevromzicafe]

Using an array is not necessary. Sheet names can go direct into a sheet.

Sub Maybe()
Dim shts, cs As String, ws As Worksheet, rng As Range, i As Long, j As Long
cs = ActiveSheet.Name
Application.ScreenUpdating = False
    For i = 1 To ThisWorkbook.Worksheets.Count
        If Sheets(i).Cells(1, 2).Value = "RVC" Then shts = shts & "," & Sheets(i).Name
    Next i
shts = Split(Mid(shts, 2), ",")
Set ws = ThisWorkbook.Worksheets.Add
Set rng = ws.Cells(1, 1).Resize(UBound(shts) + 1)
rng = Application.Transpose(shts)
rng.Sort key1:=rng, order1:=xlAscending, MatchCase:=False
    For j = 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets(ws.Cells(j, 1).Value).Move after:=Worksheets(Worksheets.Count)
    Next j
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Worksheets(cs).Select
Application.ScreenUpdating = True
End Sub

Magnificent! This does the trick. Thanks very much!

 

[jolivanes]

Thanks for letting us know and good luck.

BTW, if I remember right, rene called it "magnifique"

 

[renevromzicafe]

@jolivanes
Hi, may I add an additional complexity?
When I try to apply the procedure to a selection of sheets which name only contain numbers, I get an error message that says 'Subscript Out of Range (Run-Time Error 9)'.
When I debug the code in the VBA editor, the procedure has a problem with the line 'Worksheets(ws.Cells(j, 1).Value).Move After:=Worksheets(Worksheets.Count)"
Do you have any idea how to fix this?
Thanks a million!

 

[jolivanes]

The code you refered to in Post #4 use Sheets that have a specific value or text in a cell.
The "selection of sheets which name only contain numbers" begs for an explanation.
Do you mean the Sheet Names are numericals or does Cell B1 have a number?
If the Sheet Names are numbers and you say "selection of sheets", do have these sheets selected?
BTW, don't address a post to 1 person. There are a lot of people more capable then I am helping on these sites.

 

[renevromzicafe]

Ok, sorry if my description of the problem wasn't clear enough.
The problem occurs when the sheet names are numericals.
Thanks!

 

[jolivanes]

The code supplied by Skyybot in Post #3 works like a charm.
Try that macro first.

 

[jolivanes]

Try
Replace this line

If Sheets(i).Cells(1, 2).Value = "RVC" Then shts = shts & "," & Sheets(i).Name

with this

If Sheets(i).Cells(1, 2).Value = "RVC" Then shts = shts & "," & CStr(Sheets(i).Name)

and this line

Worksheets(ws.Cells(j, 1).Value).Move After:=Worksheets(Worksheets.Count)

with this

Worksheets(CStr(ws.Cells(j, 1).Value)).Move After:=Worksheets(Worksheets.Count)