How to use VBA Code to sort worksheets based on a pre-sorted named-range
Category: [Excel] Demo Available
How to use VBA Code to sort worksheets based on a pre-sorted named-range
Requirement:
The user has a range containing the worksheet names in my workbook, about 30 sheets. The user wants to allow the other users to sort the sheets in the order they like for printing.
The user has 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 the code then sorts that range and copies the sorted worksheet names into the 1d range on another page.
However next the user needs code to actually re-order the worksheets according to the sorted range. The user 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.
Solution:
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
Obtained from the OzGrid Help Forum.
Solution provided by jindon.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to hide/unhide specific name range using VBA |
| How to create VBA code for a workbook to work on week days only and specific range of time |
| How to count cells in a dynamic range matching two criteria given in table headers |
| How to COUNTIF using input cell as range depth |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.