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 before:=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

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 before:=Worksheets(1)
If Err <> 0 then
Err.clear ' assume sheet didn't exist
End If
Next
On Error GoTo 0 ' no more error trapping.

"[email protected]" 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 before:=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
>

Works great!! Thanks

JLatham <[email protected]> wrote:

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