Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort referenced sheets together! | Excel Worksheet Functions | |||
Sort Columns in Protected Worksheet, etc. | Excel Worksheet Functions | |||
Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions | |||
Is it possible to sort a protected worksheet? | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |