Thanks Myrna, I read through your explanation and I actually understand what
you are saying. You are a PRO !!
Jim

"Myrna Larson" wrote:

You can ignore my last response where I said I hoped you had read my previous
post. Looking at the times of it and your response to Jim, I thought maybe you
had missed it.

Glad you have it working now.

On Thu, 1 Nov 2007 14:43:00 -0700, Jim May
wrote:

Myrna,

I've never received a more comprehensive answer -- thanks so much - I've
printed out you explanation and can only hope that **after reading and
studying ** I can fully understand (as you do). I've used your suggested
Final copy and it works like I wanted it to in the first place.

Again, many thanks...
Jim May


"Myrna Larson" wrote:

As the error message says, "... With block variable not set". A statement
like

With Sheets("MySummary")

creates an internal (hidden) object variable, and the statements within the
With block operate on that object.

In your case, the object doesn't exist, so no object variable was created,
and
your on error code doesn't do anything to change that. You need to
re-execute
the With statement so that variable is created. You do that with Resume
instead of Resume Next.

Also, you've put the On Error Goto 0 statement inside the trap. That means
that if the sheet DOES exist, error trapping will never be turned off, and
that's not good. Your trap only applies to this particular statement, and
you
don't want it to execute in case of some other error, so it must be turned
off
in all cases. IOW, the On Error Goto 0 statements belongs in the main code,
not in the trap.

Also, you don't need to activate the sheet to clear it.

The code should look like this:

On Error GoTo wsAdd
Sheets("MySummary").Cells.ClearContents
On Error GoTo 0
'
'
'
Exit Sub

wsAdd:
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "MySummary"
Resume 'i.e. execute original statement again


Or you can put the whole thing in-line, like this:

Dim i As Long

On Error Resume Next
i = Sheets("MySummary").Index

If Err.Number < 0 Then
Sheets.Add(after:=Sheets(Sheets.Count)).Name = "MySummary"
End If
On Error GoTo 0

With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With

'supposedly more stuff here


On Thu, 1 Nov 2007 13:35:00 -0700, Jim May

wrote:

In my code after my DIM statements I have:

On Error GoTo wsAdd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With

Let's say MySummary (sheet) does not exist - so macro jumps to:

wsAdd:
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "MySummary"
On Error GoTo 0
Resume Next '<< Creates and Names Sheet MySummary

an returns to the line:

.Activate << Where the Macro Bombs -- What is wrong??