Subscript Out of Range

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

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).



ExcelMonkey wrote:
>
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

Tim.

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]>
wrote in message
>news:[email protected]...
>> 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
>
>
>.
>

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

ExcelMonkey wrote:
>
> 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" <[email protected]>
> wrote in message
> >news:[email protected]...
> >> 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

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?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!