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

Deleting Worksheets in VBA
 

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

Deleting Worksheets in VBA
 

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

Deleting Worksheets in VBA
 

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.

Deleting Worksheets in VBA
 

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