Worksheet Sort - but all sheets in array do not exist
 

2003

Thanks to Chuck Pearson I have the following VBA code:

Sub SortWS3()

Dim SortOrder As Variant
Dim Ndx As Long
SortOrder = Array("CSheet", "ASheet", "BSheet")
For Ndx = UBound(SortOrder) To LBound(SortOrder) Step -1
Worksheets(SortOrder(Ndx)).Move befo=Worksheets(1)
Next Ndx

End Sub

The challenge is that each workbook may not have all of the sheets.

What I need is a way for the array to SKIP the non-existant sheets OR
an alternative approach.

Any thoughts are appreciated

Thanks EagleOne

Worksheet Sort - but all sheets in array do not exist
 

I think maybe the easiest way would be to simply ignore the error created if
you try to move a sheet that doesn't exist, and then do some error cleanup if
it does happen.

On Error Resume Next ' for 'in line' error testing
For Ndx = Ubound(SortOrder) To LBound(SortOrder) Step -1
Worksheets(SortOrder(Ndx).Move befo=Worksheets(1)
If Err < 0 then
Err.clear ' assume sheet didn't exist
End If
Next
On Error GoTo 0 ' no more error trapping.

" wrote:

2003

Thanks to Chuck Pearson I have the following VBA code:

Sub SortWS3()

Dim SortOrder As Variant
Dim Ndx As Long
SortOrder = Array("CSheet", "ASheet", "BSheet")
For Ndx = UBound(SortOrder) To LBound(SortOrder) Step -1
Worksheets(SortOrder(Ndx)).Move befo=Worksheets(1)
Next Ndx

End Sub

The challenge is that each workbook may not have all of the sheets.

What I need is a way for the array to SKIP the non-existant sheets OR
an alternative approach.

Any thoughts are appreciated

Thanks EagleOne

Worksheet Sort - but all sheets in array do not exist
 

Works great!! Thanks

JLatham wrote:

On Error Resume Next ' for 'in line' error testing
For Ndx = Ubound(SortOrder) To LBound(SortOrder) Step -1
Worksheets(SortOrder(Ndx).Move befo=Worksheets(1)
If Err < 0 then
Err.clear ' assume sheet didn't exist
End If
Next