Subscript Out of Range

[ExcelMonkey]

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

 

[Dave Peterson]

I'd guess that that workbook that's active doesn't contain a worksheet named
"Audit results".

Common problems are typos and extra spaces (embedded or leading/trailing).

 

[Tom Ogilvy]

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.

 

[Tim Williams]

Maybe there is no sheet with that name? That's the usual cause of
this error

Tim.

 

[ExcelMonkey]

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

 

[Dave Peterson]

When you're testing whether a worksheet (or any object) exists, you'll want to
avoid the error you get when it doesn't.

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

Should have an "on error resume next" line near it:

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

'then this line works nicely
if not sh1 is nothing then
.....

 

[Claud Balls]

The only way I could replicate your error is by removing the Audit
Results worksheet from my workbook. Are you sure it's spelled
correctly?