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

  • 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

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


    RPerkins


    This may need adjusting to suit your range:


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


    Meaningless use of ArrayList.....

    Code
    1. Sub test()
    2. Dim r As Range
    3. For Each r In Sheets("sheet1").Cells(1).CurrentRegion.Columns(1).Cells
    4. Sheets(r.Value).Move after:=Sheets(Sheets.Count)
    5. Next
    6. Sheets("sheet1").Activate
    7. End Sub
  • 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.?

  • 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]

  • 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.

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


    Is this how you wanted?

    Code
    1. Sub test()
    2. Dim rng As Range, i As Long
    3. Set rng = Range("WorksheetNames")
    4. For i = rng.Count To 1 Step -1
    5. Sheets(rng(i).Value).Move after:=rng.Parent
    6. Next
    7. rng.Parent.Select
    8. Set rng = Nothing
    9. End Sub
  • Re: VBA Code to sort worksheets based on a pre-sorted named-range


    That means you have wrong sheet name in the list.

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


    Quote from jindon;656964

    Meaningless use of ArrayList.....

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


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

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


    Hello,


    Within the code, when you see the instruction


    Code
    1. 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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • 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:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)