On Error routine...

Hello all...

Would appreciate any help on the following...

I have an macro which loops through a series of sheets called "Day 1", "Day
2", etc. The number of "Day..." sheets will be different for each file where
the macro is run. I have set in the code a max of 10 similar routines, one
for each "Day..." sheet. As the macro activates the next Day, like "Day 2",
I have placed an on error statement before, like this:

On Error GoTo AllSheetsDone
Sheets("Day 3").Select
.... the processing code here...

On Error GoTo AllSheetsDone
Sheets("Day 4").Select
.... the processing code here...

The On Error is needed in case the next "Day..." sheet doesn't exist. Here
in this example, if there are only Day 1, Day 2 and Day 3 sheets, when the
macro tries to select Day 4, the on error should take it to some other code
to finish up.

Now, the problem. The macro seems to work fine for the first round of Day 1
through Day 3, let's say. The on error works fine. However, on the next
round of going through Day 1 through Day 3 (the macro goes through several
rounds of processing through all "Day... " sheets) the macro hangs on trying
to activate Day 4. I'm not sure why on the first round, the error routine
works fine, but on the second round, the on error doesn't work...???

Is there a way of "clearing" some error variable? I'm really guessing here.

Hope this is clear enough for someone to offer some advice.

By the way, the run time error code is 9 (Subscript out of range.)

THANKS!
Chris M

Chris,

You don't show very much code, so not sure why the error code isn't working
the 2nd time. However, I'd recommend looping through all the worksheets in
your workbook looking for the ones whose names start with "Day":

Sub test()
Dim ws As Worksheet
For Each ws In Workbooks("Book1").Worksheets
If Left$(ws.Name, 3) = "Day" Then
'your code here
End If
Next ws
End Sub

One problem with using On Error as you have is you can't know for sure that
some other error won't occur and branch to the error code with unplanned
results.

Another approach is to use a function that tests whether a given sheet
exists. This uses On Error in a more controlled way. For an example look
here:

http://www.contextures.com/xlfaqMac.html#SheetExist

hth,

Doug

"Chris M" <ChrisM@discussions.microsoft.com> wrote in message
news:E70DCBAB-8C32-46CB-A051-1492AE1D24D9@microsoft.com...
> Hello all...
>
> Would appreciate any help on the following...
>
> I have an macro which loops through a series of sheets called "Day 1",
> "Day
> 2", etc. The number of "Day..." sheets will be different for each file
> where
> the macro is run. I have set in the code a max of 10 similar routines,
> one
> for each "Day..." sheet. As the macro activates the next Day, like "Day
> 2",
> I have placed an on error statement before, like this:
>
> On Error GoTo AllSheetsDone
> Sheets("Day 3").Select
> ... the processing code here...
>
> On Error GoTo AllSheetsDone
> Sheets("Day 4").Select
> ... the processing code here...
>
> The On Error is needed in case the next "Day..." sheet doesn't exist.
> Here
> in this example, if there are only Day 1, Day 2 and Day 3 sheets, when the
> macro tries to select Day 4, the on error should take it to some other
> code
> to finish up.
>
> Now, the problem. The macro seems to work fine for the first round of Day
> 1
> through Day 3, let's say. The on error works fine. However, on the next
> round of going through Day 1 through Day 3 (the macro goes through several
> rounds of processing through all "Day... " sheets) the macro hangs on
> trying
> to activate Day 4. I'm not sure why on the first round, the error routine
> works fine, but on the second round, the on error doesn't work...???
>
> Is there a way of "clearing" some error variable? I'm really guessing
> here.
>
> Hope this is clear enough for someone to offer some advice.
>
> By the way, the run time error code is 9 (Subscript out of range.)
>
> THANKS!
> Chris M