I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub

laid this down on his screen :
I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub

Where do you store the SortWorksheetsByNameArray() procedure? Is it
scoped 'Private' to a specific module outside the module where you have
this code? More info is needed to better help you...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Tue, 31 Jan 2012 17:47:15 -0500, GS wrote:

laid this down on his screen :
I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub

Where do you store the SortWorksheetsByNameArray() procedure? Is it
scoped 'Private' to a specific module outside the module where you have
this code? More info is needed to better help you...


The SortWorksheetsByNameArray is declared as a Public Function

It is in a different workbook module however.

The error I get is type mismatch.

on 2/1/2012, supposed :
On Tue, 31 Jan 2012 17:47:15 -0500, GS wrote:

laid this down on his screen :
I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub

Where do you store the SortWorksheetsByNameArray() procedure? Is it
scoped 'Private' to a specific module outside the module where you have
this code? More info is needed to better help you...


The SortWorksheetsByNameArray is declared as a Public Function

It is in a different workbook module however.

The error I get is type mismatch.

You may need to ref the VBA project the function is in. It would be
easier if you copy it into a standard module in your project.

Also, post the function's declaration so we can see what it needs for
Type of args being passed in.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Wed, 01 Feb 2012 10:56:17 -0500, GS wrote:

on 2/1/2012, supposed :
On Tue, 31 Jan 2012 17:47:15 -0500, GS wrote:

laid this down on his screen :
I am trying to use the code for sorting worksheets by a named array
from this website http://www.cpearson.com/excel/SortWS.aspx

Sub SortMySheets()

Dim NameArray As Variant
NameArray = Range("mysheets").Value

'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

SortWorksheetsByNameArray (NameArray()) <== I keep getting errors
here, think i have tried almost everything



End Sub

Where do you store the SortWorksheetsByNameArray() procedure? Is it
scoped 'Private' to a specific module outside the module where you have
this code? More info is needed to better help you...


The SortWorksheetsByNameArray is declared as a Public Function

It is in a different workbook module however.

The error I get is type mismatch.

You may need to ref the VBA project the function is in. It would be
easier if you copy it into a standard module in your project.

Also, post the function's declaration so we can see what it needs for
Type of args being passed in.

I moved this to a module in the workbook it would be used.

I have a vertical list on a worksheet which indicates the order I want
the worksheets ( tabs ) sorted in the workbook.

Thats why I have NameArray = Range("mysheets").Value, this part
works, as I can scroll through the text of the list with
'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next

The problem is I can't seem to call the function (code below) from
within VBA to sort the Sheets (tabs)

Appreciate all the help.

Public Function SortWorksheetsByNameArray(NameArray() As Variant, _
ByRef ErrorText As String, Optional WhatWorkbook As Workbook) As
Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
' WorksheetSortByArray
' This procedure sorts the worksheets named in NameArray to the order
in' which they appear in NameArray. The adjacent elements in NameArray
need
' not be adjacent sheets, but the collection of all sheets named in
' NameArray must form a set of adjacent sheets. If successful, returns
' True and ErrorText is vbNullString. If failure, returns False and
' ErrorText contains reason for failure. WhatWorkbook specifies the
' workbook containing the sheets to sort. If omitted, the
ActiveWorkbook
' is used.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''
Dim Arr() As Long
Dim N As Long
Dim M As Long
Dim L As Long
Dim WB As Workbook

If WhatWorkbook Is Nothing Then
Set WB = ActiveWorkbook
Else
Set WB = WhatWorkbook
End If

ErrorText = vbNullString

'''''''''''''''''''''''''''''''''''''''''''''''
' The NameArray need not contain all of the
' worksheets in the workbook, but the sheets
' that it does name together must form a group of
' adjacent sheets. Sheets named in NameArray
' need not be adjacent in the NameArray, only
' that when all sheet taken together, they form an
' adjacent group of sheets
'''''''''''''''''''''''''''''''''''''''''''''''
ReDim Arr(LBound(NameArray) To UBound(NameArray))
'On Error Resume Next
For N = LBound(NameArray) To UBound(NameArray)
'''''''''''''''''''''''''''''''''''''''
' Ensure all sheets in name array exist
'''''''''''''''''''''''''''''''''''''''
Err.Clear
M = Len(WB.Worksheets(NameArray(N)).Name) '<<<
If Err.Number < 0 Then
ErrorText = "Worksheet does not exist."
SortWorksheetsByNameArray = False
Exit Function
End If
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
' Put the index value of the sheet into Arr. Ensure there
' are no duplicates. If Arr(N) is not zero, we've already
' loaded that element of Arr and thus have duplicate sheet
' names.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
If Arr(N) 0 Then
ErrorText = "Duplicate worksheet name in NameArray."
SortWorksheetsByNameArray = False
Exit Function
End If

Arr(N) = Worksheets(NameArray(N)).Index
Next N

'''''''''''''''''''''''''''''''''''''''
' Sort the sheet indexes. We don't use
' these for the sorting order, but we
' do use them to ensure that the group
' of sheets passed in NameArray are
' together contiguous.
'''''''''''''''''''''''''''''''''''''''
For M = LBound(Arr) To UBound(Arr)
For N = M To UBound(Arr)
If Arr(N) < Arr(M) Then
L = Arr(N)
Arr(N) = Arr(M)
Arr(M) = L
End If
Next N
Next M
''''''''''''''''''''''''''''''''''''''''
' Now that Arr is sorted ascending, ensure
' that the elements are in order differing
' by exactly 1. Otherwise, sheet are not
' adjacent.
'''''''''''''''''''''''''''''''''''''''''
If ArrayElementsInOrder(Arr:=Arr, Descending:=False, Diff:=1) = False
Then
ErrorText = "Specified sheets are not adjacent."
SortWorksheetsByNameArray = False
Exit Function
End If

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
' Now, do the actual move of the sheets.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
On Error GoTo 0
WB.Worksheets(NameArray(LBound(NameArray))).Move
befo=WB.Worksheets(Arr(1))
For N = LBound(NameArray) + 1 To UBound(NameArray) - 1
WB.Worksheets(NameArray(N)).Move befo=WB.Worksheets(NameArray(N
+ 1))
Next N

SortWorksheetsByNameArray = True


End Function

Thanks for posting back!
There's good reasons for an error being raised...

Firstly, your call is missing args needed by the function. It takes 3
args, the first 2 of those are compulsory while the 3rd is optional.

Secondly, the array being passed to the function needs to be 1D. You
are trying to pass a 2D array. You need to put the list into a temp
array BEFORE passing it to the function.
--

As for the function structure itself, IMO it seems a bit
'long-in-tooth' for the task at hand. I'd certainly want to revise this
to something more efficient for the task. If all you're doing is
reordering specific sheets to be adjacent to each other as per the same
order they appear in NameArray, only one loop is necessary to
accomplish this (even if the first sheet in the list stays put and the
remaining sheets stack up beside it). Do you mind telling me where you
got this function? (It's 'style' looks vaguely familiar)

I'll try to come up with something that will work better for you in
your scenario, and post back...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thats exactly what I am trying to do

I have a Workbook with maybe 40 sheets, the first sheet in the
Workbook has a list of the worksheets, I have named this Range
MySheets. I want to sort the SHEET TABS in the same order as this
Range.

Sub TestArray()
NameArray = Range("MySheets").Value
'Looping structure to look at array.
For i = 1 To UBound(NameArray)
MsgBox NameArray(i, 1)
Next
End Sub

I know this works to create the Array

My Problem is transferring this Array Information to the
SortWorksheetsByNameArray Function

Appreciate the help

On Wed, 01 Feb 2012 13:41:05 -0500, GS wrote:

Thanks for posting back!
There's good reasons for an error being raised...

Firstly, your call is missing args needed by the function. It takes 3
args, the first 2 of those are compulsory while the 3rd is optional.

Secondly, the array being passed to the function needs to be 1D. You
are trying to pass a 2D array. You need to put the list into a temp
array BEFORE passing it to the function.
--

As for the function structure itself, IMO it seems a bit
'long-in-tooth' for the task at hand. I'd certainly want to revise this
to something more efficient for the task. If all you're doing is
reordering specific sheets to be adjacent to each other as per the same
order they appear in NameArray, only one loop is necessary to
accomplish this (even if the first sheet in the list stays put and the
remaining sheets stack up beside it). Do you mind telling me where you
got this function? (It's 'style' looks vaguely familiar)

I'll try to come up with something that will work better for you in
your scenario, and post back...

So then, all the sheetnames in the list are to follow the first sheet
(which contains the list), and any sheets not in the list remain in
their present order AFTER the reordered sheets?

Also, I assume the named range for the list has global scope (workbook
level as apposed to sheet level)?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

You are correct, the Cover Sheet will always be at the front of the
Workbook and never move. The List (Range) does not reference itself.

However, as a caviat, all Names in the Range may not actually be a
Sheet. Thus, will need a test as to if the Sheet(s) exist or not.

Not sure if it matters, but the Workbook does contain hidden sheets,
to the left of the Cover Sheet.

On Wed, 01 Feb 2012 14:56:48 -0500, GS wrote:

So then, all the sheetnames in the list are to follow the first sheet
(which contains the list), and any sheets not in the list remain in
their present order AFTER the reordered sheets?

Also, I assume the named range for the list has global scope (workbook
level as apposed to sheet level)?

The Test_ sub show exactly how to call the code when passing a range
address OR a delimited string. Use only one method. Once the 1st method
has run the sheets should be reordered as per your list, thus the 2nd
method wouldn't make any changes to that reorder unless you manually
reposition the list sheet and run either method again.

You told me the name of the list range was "MySheets" and so is what I
used in my tests. All worked exactly as expected for me and so YES, you
must be doing something wrong.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thank you your comment made perfect sense now, I seperated the code
out and it worked

Thank you so much, this is awesome.

I really appreciate your time and effort.

William Wells

On Thu, 02 Feb 2012 09:20:39 -0500, GS wrote:

The Test_ sub show exactly how to call the code when passing a range
address OR a delimited string. Use only one method. Once the 1st method
has run the sheets should be reordered as per your list, thus the 2nd
method wouldn't make any changes to that reorder unless you manually
reposition the list sheet and run either method again.

You told me the name of the list range was "MySheets" and so is what I
used in my tests. All worked exactly as expected for me and so YES, you
must be doing something wrong.

I'm glad to see you got it working! Thanks for letting me know. Always
glad to be of help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc