Worksheet Sort - but all sheets in array do not exist

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

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
 
Back
Top