Solved: Sort worksheets using a Range as the Sort Order

[JimS]

I need to sort several worksheets within an active workbook.
I was thinking of creating the sort order on a sheet named "Order" (cell A1:??). I will create this sort order list with some formulas so the code would need to ignore formulas (or delete any formulas first).


I have found the code below but cannot get it to work.

It always fails on: Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
I haven't tried to use a dynamic range name yet - wanted to make sure this code worked first.

My Workbook has Charts in it - would that make a difference?

Any ideas?

Thanks...

Jim


Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long

Application. ScreenUpdating (http://vbaexpress.com/forum/) = False

With Worksheets("Sort Order").Range("A1:A79")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With

Application.ScreenUpdating = True

End Sub

[mdmackillop]

Hi Jim,
Use Sheets instead of Worksheet: also .Cells(Ndx,1)

Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long

Application.ScreenUpdating = False

With Sheets("SortOrder").Range("A1:A4")
For Ndx = .Cells.Count To 1 Step -1
Sheets(.Cells(Ndx, 1).Value).Move before:=Sheets(1)
Next Ndx
End With

Application.ScreenUpdating = True

End Sub

[JimS]

mdmackillop,

It must be me, I still can't get this to work.

I have attached an example of the Workbook with the Sheets that I'm trying to sort.

Thanks...

Jim

[Benzadeus]

Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long

Application.ScreenUpdating = False

With Sheets("SortOrder").Range("A1:A7")
For Ndx = .Cells.Count To 1 Step -1
Sheets(.Cells(Ndx, 1).Value).Move After:=Sheets(1)
Next Ndx
End With

Application.ScreenUpdating = True

End Sub

[mdmackillop]

Hi Jim,
Very sneaky!
"…" in Start Here… Cell A7 is a single character Code 133 (called ellipsis, I believe)
In the Sheet tab name "..." is three period marks, Code 46
so the Cell text is not the same as the tab name.

Also, in the code, set the range from A1:A7, or better yet, do it dynamically.

[JimS]

Samething, fails with a Script Out of Range error at:

Sheets(.Cells(Ndx, 1).Value).Move After:=Sheets(1)

[JimS]

mdmackillop,

Not sure how you found that but that is it.

I can do without the "..."

Thanks to both of you!

Jim

[mdmackillop]

You probably have an AutoCorrect that made the change, unless you copied it from elswhere.