Here is the problem. I am in an xla file. The routine
inserts a sheet called "Audit Results". If its already
there from a prevous run it deletes it. I was using
dilogue sheets for temporay userforms to summarize sheets
in the model. Just changed this to a list box
userform. I was replacing "Thisworkbook"
with "ActiveWorkbook". Now I am confused. This was
working fine. It only seems to work when sheet already
exists called "Audit Results". Previously it worked even
if one did not exist, because it would creat it.

Here is what I have for code:


'Set up name of new summary sheet
Set sh1 = ActiveWorkbook.Sheets("Audit Results")
On Error GoTo 0

'If Sheet called "Audit Results" already exists
'then delete it and prepare to create a new one

If Not sh1 Is Nothing Then
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End If

With ActiveWorkbook

'Add a worksheet for results to be pasted to
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = "Audit Results"

End With

-----Original Message-----
You don't have a worksheet in the activeworkbook named
Audit Results. If
you did, you wouldn't get the error. If you didn't
think you did you
wouldn't have written the code - so you need to look
closer. Is there an
extra space somewhere - on either end or perhaps an
extra spaced separating
the two words.

--
Regards,
Tom Ogilvy

"ExcelMonkey"
wrote in message
...
I am getting an Subscript Out of Range Error on this
line
of code:

Set sh1 = ActiveWorkbook.Sheets("Audit Results")

Why is this happening?

Thanks


.