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