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" wrote in message
...
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
...
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" wrote in message
...
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 ,
"Knut Dahl" 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" wrote in message
...
Ah yeah of course.

Thanks so much. Really appreciate it.

KJ

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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" wrote in message
...
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