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 els
wrong?

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

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

Do I have to reset the "On Error" condition or am I doing somethin
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 doe
Not exist
Worksheets.Add.Name = (sSheetTo)
End If

Next

 

[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]

Set wsSheet = Nothing

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

Thank You, Rob!