VBA Code to sort worksheets based on a pre-sorted named-range

rdperkins · Mar 30th 2013

I have a range containing the worksheet names in my workbook, about 30 sheets. I want to allow the users to sort the sheets in the order they like for printing. I have another range containing the worksheet names and an 'order' column, with 1-n numbers. The user would just change the numbers in the sort order, and my code then sorts that range and copies the sorted worksheet names into the 1d range on another page. So far so good. However next I need code to actually re-order the worksheets according to the sorted range. I found a function on a lot of sites made by C.Pearson that accepts an array of names, but it gives no subroutine showing how to create the array or invoke the function. I also saw comments that it was a bit convoluted, but can't comment. Hopefully someone either has solved this problem differently and can post a solution, or has the code and instructions to use Pearson's "SortWorksheetsByNameArray" function.

TIA,

RPerkins

dangle · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

RPerkins

This may need adjusting to suit your range:

Code

Sub SortWS()
Dim i As Long, r As Range
Application.ScreenUpdating = False
With CreateObject("System.Collections.SortedList")
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Item(r.Offset(, 1).Value) = r.Value
    Next
    For i = 0 To .Count - 1
        Sheets(.getbyindex(i)).Move after:=Sheets(.Count)
    Next
End With
Application.ScreenUpdating = True
End Sub

Display More

jindon · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Meaningless use of ArrayList.....

Code

Sub test()
    Dim r As Range
    For Each r In Sheets("sheet1").Cells(1).CurrentRegion.Columns(1).Cells
        Sheets(r.Value).Move after:=Sheets(Sheets.Count)
    Next
    Sheets("sheet1").Activate
End Sub

rdperkins · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

I tried Dangle's code but must not have changed the right bits. I tried Jindon's code, and it worked in his example for the sheets named sheet1, sheet2, etc., but my sheets have been renamed. I renamed one of the sheets in his example and on page 1 of the list supplying the new order, but it didn't work anymore. Is there a way to change the code to reference the sheet name property instead of Sheet1, etc.?

jindon · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

When you rename the sheet name, the list in Sheet1 MUST be changed to that sheet name.

You can not move any sheet that doesn't exist.

rdperkins · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Dear Jindon,

Works great in your worksheet. I guess I wasn't awake at 4:00AM when I tried it the first time. I do need a bit more help though. My list isn't on Sheet1 starting in cell A1. It is in a named range called "WorksheetNames" on the first worksheet in the workbook, one that like Sheet1 in your example won't be sorted. It also doesn't start in A1 and is subject to move as I may adjust the layout on that page in the future. One other thing that might be important is that the first sheet is not the only one not being sorted. I have about 4 or 5 sheets at the end that I exclude from printing and which will never be part of the WorksheetNames list.

What changes need to be made in your code to use the named range instead of cell A1 on Sheet1? I've taken a stab at he in the following code but I get a subscript out of range error.

[Start of Code]
Sub TestSort()
Dim r As Range, cnt As Long, Rng As Range
Set Rng = Range("WorksheetNames")
cnt = Sheet20.Range("PrintableSheetCount").Value 'a count formula in this named cell contains the correct number of sheetnames to be re-ordered.
Rng.Select
For Each r In Rng
Sheets(r.Value).Move after:=Sheets(cnt)
Next
Sheets("Setup").Activate
End Sub
[End of Code]
[TABLE="width: 168"]

[tr]

[td][/td]

[/tr]

[/TABLE]

jindon · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

OK

Where do you want those sheet(s) that are not in the list?

Or, in other words, how do you want the sheets in the list to be sorted?

rdperkins · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

The 'Contents' page (menu) containing the range "WorksheetNames" is the first sheet and won't be sorted.
Next come 23 worksheets that will be sorted.
Finally there are 4 non-sorted worksheets that will always stay at the end.

jindon · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Is this how you wanted?

Code

Sub test()
    Dim rng As Range, i As Long
    Set rng = Range("WorksheetNames")
    For i = rng.Count To 1 Step -1
        Sheets(rng(i).Value).Move after:=rng.Parent
    Next
    rng.Parent.Select
    Set rng = Nothing
End Sub

rdperkins · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Might be. I get a subscript out of range on "Sheets(rng(i).Value).Move after:=rng.Parent"

jindon · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

That means you have wrong sheet name in the list.

Code

Sub test()
    Dim rng As Range, i As Long, msg As String
    Set rng = Range("WorksheetNames")
    For i = rng.Count To 1 Step -1
        If rng(i).Value <> "" Then
            If IsSheetExists(rng(i).Value) Then
                Sheets(rng(i).Value).Move after:=rng.Parent
            Else
                msg = msg & vbLf & rng(i).Value
            End If
        End If
    Next
    rng.Parent.Select
    If Len(msg) Then MsgBox "Wrong sheet name" & msg
    Set rng = Nothing
End Sub




Function IsSheetExists(txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

Display More

rdperkins · Mar 31st 2013

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Brilliant! Many thanks and kudos.
I believe what you are doing is sorting the last sheet in place, then next to last.... Is this correct?

knonist · Feb 25th 2014

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Quote from jindon;656964

Meaningless use of ArrayList.....

Code

Sub test()
    Dim r As Range
    For Each r In Sheets("sheet1").Cells(1).CurrentRegion.Columns(1).Cells
        Sheets(r.Value).Move after:=Sheets(Sheets.Count)
    Next
    Sheets("sheet1").Activate
End Sub

Thanks, Have been looking for something like this for a whole day!

manoj mailapur · Aug 30th 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Run Time error'1004' And Shows An Yellow mark OnSet rng = Range("WorksheetNames")
Pls Help Me On this Error on These Code

**Carim** · Aug 30th 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Hello,

Within the code, when you see the instruction

Code

Set rng=Range("WorksheetNames")

it means that, beforehand, you have created a named range which is listing all the names of all your worksheets ...

Hope this will help

manoj mailapur · Aug 31st 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

I Can't Understand the Wordings, Because Am Not An Expert Can You Please Explain In An prudent man manner

BaraaKhalil · Aug 31st 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Hello manoh
[ATTACH=CONFIG]73726[/ATTACH]

**Carim** · Aug 31st 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Quote from BaraaKhalil;799289

Hello manoh
[ATTACH=CONFIG]73726[/ATTACH]

@BaraaKhahil ...

Thanks a lot for your very clear explanation .... :smile:

BaraaKhalil · Aug 31st 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

You're welcome Mr. Carim
Glad you liked it
Regards

manoj mailapur · Sep 1st 2017

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Thanks for Yours Clear Explanation.

VBA Code to sort worksheets based on a pre-sorted named-range

Participate now!

Share