On Error? Creates 1 missing worksheet then never detects any other missing worksheets

[Craigm]

Do I have to reset the "On Error" condition or am I doing something else wrong?

I am trying to run through a series of specific worksheets amoung many worksheets. If the sheet exists then clear the entire worksheet. If the sheet does not exist then create it.

It creates the first missing worksheet without any problem. When it encounters the second missing sheet it never fails the "If Not wsSheet Is Nothing Then" test. Meaning it never enters the "Else" condition again. So it does not create any of the missing sheets and crashes on "Worksheets(sSheetTo).Cells.Clear" when it tries to clear the missing worksheet.

Do I have to reset the "On Error" condition or am I doing something else wrong?

Respectfully,

Craigm

'===========================================
Dim wsSheet As Worksheet 'For worksheet exists only

sSheetFrom = "Inventory"

For k = 1 To 9
If k = 1 Then
sSheetTo = "Auto"
ElseIf k = 2 Then
sSheetTo = "Trucks"
ElseIf k = 3 Then
sSheetTo = "Vans"
ElseIf k = 4 Then
sSheetTo = "Boats"
ElseIf k = 5 Then
sSheetTo = "Airplanes"
ElseIf k = 6 Then
sSheetTo = "Motorcycles"
ElseIf k = 7 Then
sSheetTo = "Trailers"
ElseIf k = 8 Then
sSheetTo = "SUVs"
ElseIf k = 9 Then
sSheetTo = "Unknown"
End If

''''We need to run through this and clear all worksheets.
'If it does EXIST then clear its contents out
'If the passed in WorkSheet does not EXIST then create it.

On Error Resume Next
Set wsSheet = Worksheets(sSheetTo)
On Error GoTo 0
If Not wsSheet Is Nothing Then'..................It does Exist
wsSheet.Activate
Worksheets(sSheetTo).Cells.Clear
Else'...........................................................It does Not exist
Worksheets.Add.Name = (sSheetTo)
End If

Next k

[Rob Bovey]

Hi Craig,

Just taking a guess without having to set all this up, but in the
following lines of code:

On Error Resume Next
Set wsSheet = Worksheets(sSheetTo)
On Error GoTo 0

Once your code has encountered a worksheet that exists it sets the wsSheet
variable to reference that sheet. You never set this variable back to
Nothing, so on subsequent loops it still maintains a reference to whatever
the last worksheet it located was. Try it like this:

Set wsSheet = Nothing
On Error Resume Next
Set wsSheet = Worksheets(sSheetTo)
On Error GoTo 0


--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Craigm" <[email protected]> wrote in
message news:[email protected]...
>
> Do I have to reset the "On Error" condition or am I doing something else
> wrong?
>
> I am trying to run through a series of specific worksheets amoung many
> worksheets. If the sheet exists then clear the entire worksheet. If
> the sheet does not exist then create it.
>
> It creates the first missing worksheet without any problem. When it
> encounters the second missing sheet it never fails the "If Not wsSheet
> Is Nothing Then" test. Meaning it never enters the "Else" condition
> again. So it does not create any of the missing sheets and crashes on
> "Worksheets(sSheetTo).Cells.Clear" when it tries to clear the missing
> worksheet.
>
> Do I have to reset the "On Error" condition or am I doing something
> else wrong?
>
> Respectfully,
>
> Craigm
>
> '===========================================
> Dim wsSheet As Worksheet 'For worksheet exists only
>
> sSheetFrom = "Inventory"
>
> For k = 1 To 9
> If k = 1 Then
> sSheetTo = "Auto"
> ElseIf k = 2 Then
> sSheetTo = "Trucks"
> ElseIf k = 3 Then
> sSheetTo = "Vans"
> ElseIf k = 4 Then
> sSheetTo = "Boats"
> ElseIf k = 5 Then
> sSheetTo = "Airplanes"
> ElseIf k = 6 Then
> sSheetTo = "Motorcycles"
> ElseIf k = 7 Then
> sSheetTo = "Trailers"
> ElseIf k = 8 Then
> sSheetTo = "SUVs"
> ElseIf k = 9 Then
> sSheetTo = "Unknown"
> End If
>
> ''''We need to run through this and clear all worksheets.
> 'If it does EXIST then clear its contents out
> 'If the passed in WorkSheet does not EXIST then create it.
>
> On Error Resume Next
> Set wsSheet = Worksheets(sSheetTo)
> On Error GoTo 0
> If Not wsSheet Is Nothing Then'..................It does Exist
> wsSheet.Activate
> Worksheets(sSheetTo).Cells.Clear
>
> Else'...........................................................It does
> Not exist
> Worksheets.Add.Name = (sSheetTo)
> End If
>
> Next k
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
> http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=391774
>

[Craigm]

Set wsSheet = Nothing

I had to put the line in both the "If" and "Else" conditions. Now it works correctly.

Thank You, Rob!