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

try
On Error GoTo wsadd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With
wsadd:
Sheets.Add
ActiveSheet.Name = "MySummary"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jim May" wrote in message
...
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??

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

Don thanks, but I have about 30 lines of code between the:

End With

and the

wsadd:

and I was hoping to take advantage of the Resume Next line

Your suggested code look almost the same as what I have already...
hummmmmm.. (in doubt..)
Tks,

Jim


"Don Guillett" wrote:

try
On Error GoTo wsadd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With
wsadd:
Sheets.Add
ActiveSheet.Name = "MySummary"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jim May" wrote in message
...
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??

Give this a whirl. It avoids jumping around by using a worksheet object...

dim wksSummary as worksheet

on error resume next
set wksSummary = Sheets("Summary")
on error goto 0

if wksSummary is nothing then
sheets.add
activesheet.name = "Summary"
end if
'continue your code here...
--
HTH...

Jim Thomlinson


"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??

Hi, Don:

A couple of comments:

You need an Exit Sub statement above the wsadd: line, so that, when the sheet
DOES exist, you won't add another sheet and give it the same name.

IMO, it's not good practice to "fall into" error trapping code when no error
has occurred. That can cause other errors. For example, if the trap included a
Resume statement, that would cause another error, Resume Without Error, IIRC.

But you don't have a Resume statement. If the trap code is at the bottom of
the sub (as is usually the case), the sub will exit after adding the
worksheet. I guess that's OK if the sub ONLY clears the contents of this
sheet. If it does something else, like entering some headers, formatting, etc,
you need to Resume, and with a RESUME statement rather than RESUME NEXT.

If there is more work to be done, to eliminate the redundancy of clearing a
known-empty worksheet, you could Resume at a statement after the ClearContents
line.

On Thu, 1 Nov 2007 15:47:25 -0500, "Don Guillett"
wrote:

try
On Error GoTo wsadd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With
wsadd:
Sheets.Add
ActiveSheet.Name = "MySummary"

Slick Jim - I will give it a go...
Thanks,
Jim

"Jim Thomlinson" wrote:

Give this a whirl. It avoids jumping around by using a worksheet object...

dim wksSummary as worksheet

on error resume next
set wksSummary = Sheets("Summary")
on error goto 0

if wksSummary is nothing then
sheets.add
activesheet.name = "Summary"
end if
'continue your code here...
--
HTH...

Jim Thomlinson


"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??

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

I hope you have taken note of my comments re the Resume vs Resume Next issue,
and the placement of the On Error Goto 0 statements.

Particularly the latter. You don't want to execute this particular trap for
some error in your code later down.



On Thu, 1 Nov 2007 14:38:03 -0700, Jim May
wrote:

Slick Jim - I will give it a go...
Thanks,
Jim

"Jim Thomlinson" wrote:

Give this a whirl. It avoids jumping around by using a worksheet object...

dim wksSummary as worksheet

on error resume next
set wksSummary = Sheets("Summary")
on error goto 0

if wksSummary is nothing then
sheets.add
activesheet.name = "Summary"
end if
'continue your code here...
--
HTH...

Jim Thomlinson


"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??

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

One more option...

If you don't have anything against adding the sheet, maybe it's ok to just
delete it and start from scratch:

on error resume next
worksheets("mySummary").delete
on error goto 0
'then add the sheet.

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

--

Dave Peterson

Thanks Dave, I understand your code..

"Dave Peterson" wrote:

One more option...

If you don't have anything against adding the sheet, maybe it's ok to just
delete it and start from scratch:

on error resume next
worksheets("mySummary").delete
on error goto 0
'then add the sheet.

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

--

Dave Peterson

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

although he'll probably want to suppress the "are you sure" message

application.displayalerts = false
on error resume next
worksheets("mySummary").delete
on error goto 0
application.displayalerts = true


"Dave Peterson" wrote:

One more option...

If you don't have anything against adding the sheet, maybe it's ok to just
delete it and start from scratch:

on error resume next
worksheets("mySummary").delete
on error goto 0
'then add the sheet.

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

--

Dave Peterson

Thanks for the catch
Exit Sub statement
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Myrna Larson" wrote in message
...
Hi, Don:

A couple of comments:

You need an Exit Sub statement above the wsadd: line, so that, when the
sheet
DOES exist, you won't add another sheet and give it the same name.

IMO, it's not good practice to "fall into" error trapping code when no
error
has occurred. That can cause other errors. For example, if the trap
included a
Resume statement, that would cause another error, Resume Without Error,
IIRC.

But you don't have a Resume statement. If the trap code is at the bottom
of
the sub (as is usually the case), the sub will exit after adding the
worksheet. I guess that's OK if the sub ONLY clears the contents of this
sheet. If it does something else, like entering some headers, formatting,
etc,
you need to Resume, and with a RESUME statement rather than RESUME NEXT.

If there is more work to be done, to eliminate the redundancy of clearing
a
known-empty worksheet, you could Resume at a statement after the
ClearContents
line.

On Thu, 1 Nov 2007 15:47:25 -0500, "Don Guillett"

wrote:

try
On Error GoTo wsadd
With Sheets("MySummary")
.Activate
.Cells.ClearContents
End With
wsadd:
Sheets.Add
ActiveSheet.Name = "MySummary"