Deleting Worksheets in VBA

Good afternoon everyone.
I have yet another problem that is doing my head in.
I am trying to programmatically delete all worksheets in a workbook except
the first one.
I have tried the following:

Dim p As Integer
Dim q As Integer
p = Worksheets.Count
If p >= 2 Then
For q = 2 To p
Worksheets(q).Delete
Next q
End If

This however gives me a 'Subscript out of range' if there are more than 2
sheets in the workbook The Debugger tells me that there is something wrong
with:

Worksheets(q).Delete

Hope this is enough information.
Thanks in advance for any help.

KJ

You need to count backwards so that the worksheet index doesn't change after
each deletion:

For q = p To 2 Step -1

--

Vasant


"Knut Dahl" <funboy_1972@yahoo.co.uk> wrote in message
news:d6t36b$r9j$1@news7.svr.pol.co.uk...
> Good afternoon everyone.
> I have yet another problem that is doing my head in.
> I am trying to programmatically delete all worksheets in a workbook except
> the first one.
> I have tried the following:
>
> Dim p As Integer
> Dim q As Integer
> p = Worksheets.Count
> If p >= 2 Then
> For q = 2 To p
> Worksheets(q).Delete
> Next q
> End If
>
> This however gives me a 'Subscript out of range' if there are more than 2
> sheets in the workbook The Debugger tells me that there is something wrong
> with:
>
> Worksheets(q).Delete
>
> Hope this is enough information.
> Thanks in advance for any help.
>
> KJ
>
>

Ah yeah of course.

Thanks so much. Really appreciate it.

KJ

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:OKGLBy7XFHA.3280@TK2MSFTNGP09.phx.gbl...
> You need to count backwards so that the worksheet index doesn't change
> after
> each deletion:
>
> For q = p To 2 Step -1
>
> --
>
> Vasant
>
>
> "Knut Dahl" <funboy_1972@yahoo.co.uk> wrote in message
> news:d6t36b$r9j$1@news7.svr.pol.co.uk...
>> Good afternoon everyone.
>> I have yet another problem that is doing my head in.
>> I am trying to programmatically delete all worksheets in a workbook
>> except
>> the first one.
>> I have tried the following:
>>
>> Dim p As Integer
>> Dim q As Integer
>> p = Worksheets.Count
>> If p >= 2 Then
>> For q = 2 To p
>> Worksheets(q).Delete
>> Next q
>> End If
>>
>> This however gives me a 'Subscript out of range' if there are more than 2
>> sheets in the workbook The Debugger tells me that there is something
>> wrong
>> with:
>>
>> Worksheets(q).Delete
>>
>> Hope this is enough information.
>> Thanks in advance for any help.
>>
>> KJ
>>
>>
>
>

Say you have 10 sheets. Once you iterate 5 times, deleting sheets 2-6,
you have only 5 sheets left in the Worksheets collection (1,7,8,9,10).
But on the next iteration, q = 6, so Worksheets(q) is out of range. Try:


For q = p To 2 Step -1


instead.

In article <d6t36b$r9j$1@news7.svr.pol.co.uk>,
"Knut Dahl" <funboy_1972@yahoo.co.uk> wrote:

> Good afternoon everyone.
> I have yet another problem that is doing my head in.
> I am trying to programmatically delete all worksheets in a workbook except
> the first one.
> I have tried the following:
>
> Dim p As Integer
> Dim q As Integer
> p = Worksheets.Count
> If p >= 2 Then
> For q = 2 To p
> Worksheets(q).Delete
> Next q
> End If
>
> This however gives me a 'Subscript out of range' if there are more than 2
> sheets in the workbook The Debugger tells me that there is something wrong
> with:
>
> Worksheets(q).Delete
>
> Hope this is enough information.
> Thanks in advance for any help.

another option is to always delete the second sheet

for q = 2 to p
worksheets(2).Delete
Next

--
Regards.
Tom Ogilvy



"Knut Dahl" <funboy_1972@yahoo.co.uk> wrote in message
news:d6t3tr$u8e$1@news6.svr.pol.co.uk...
> Ah yeah of course.
>
> Thanks so much. Really appreciate it.
>
> KJ
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:OKGLBy7XFHA.3280@TK2MSFTNGP09.phx.gbl...
> > You need to count backwards so that the worksheet index doesn't change
> > after
> > each deletion:
> >
> > For q = p To 2 Step -1
> >
> > --
> >
> > Vasant
> >
> >
> > "Knut Dahl" <funboy_1972@yahoo.co.uk> wrote in message
> > news:d6t36b$r9j$1@news7.svr.pol.co.uk...
> >> Good afternoon everyone.
> >> I have yet another problem that is doing my head in.
> >> I am trying to programmatically delete all worksheets in a workbook
> >> except
> >> the first one.
> >> I have tried the following:
> >>
> >> Dim p As Integer
> >> Dim q As Integer
> >> p = Worksheets.Count
> >> If p >= 2 Then
> >> For q = 2 To p
> >> Worksheets(q).Delete
> >> Next q
> >> End If
> >>
> >> This however gives me a 'Subscript out of range' if there are more than
2
> >> sheets in the workbook The Debugger tells me that there is something
> >> wrong
> >> with:
> >>
> >> Worksheets(q).Delete
> >>
> >> Hope this is enough information.
> >> Thanks in advance for any help.
> >>
> >> KJ
> >>
> >>
> >
> >
>
>