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