Delete Multiple sheets
 

Hi all,

I have a macro that creates upto about 15 sheets of pivot tables and
then copies the data onto other sheets. I would like to know how I can
delete all of these sheets as they are not needed.

These sheets all have the standard new sheet name. I.e. Sheetxx

The VBA Code for this would be tremendous :)


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=508547

Delete Multiple sheets
 

This should do the trick. Put it in a standard module...

Sub DeleteSheets()
Dim wks As Worksheet
On Error GoTo ErrorHandler
Application.DisplayAlerts = False
For Each wks In Worksheets
If Left(wks.Name, 5) = "Sheet" Then wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = False
End Sub
--
HTH...

Jim Thomlinson


"moglione1" wrote:


Hi all,

I have a macro that creates upto about 15 sheets of pivot tables and
then copies the data onto other sheets. I would like to know how I can
delete all of these sheets as they are not needed.

These sheets all have the standard new sheet name. I.e. Sheetxx

The VBA Code for this would be tremendous :)


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=508547

Delete Multiple sheets
 

Try this:

After your done with the data on each sheet, before you create the next
sheet use ActiveSheet.Delete if it's the active sheet; -use your object
variable for the sheet if it's not!

Regards, GS

"moglione1" wrote:


Hi all,

I have a macro that creates upto about 15 sheets of pivot tables and
then copies the data onto other sheets. I would like to know how I can
delete all of these sheets as they are not needed.

These sheets all have the standard new sheet name. I.e. Sheetxx

The VBA Code for this would be tremendous :)


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=508547

Delete Multiple sheets
 

Hi Jim,

You should be turning .DisplayAlerts back on (TRUE) in your ErrorHandler:
Regards, GS

"Jim Thomlinson" wrote:

This should do the trick. Put it in a standard module...

Sub DeleteSheets()
Dim wks As Worksheet
On Error GoTo ErrorHandler
Application.DisplayAlerts = False
For Each wks In Worksheets
If Left(wks.Name, 5) = "Sheet" Then wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = False
End Sub
--
HTH...

Jim Thomlinson


"moglione1" wrote:


Hi all,

I have a macro that creates upto about 15 sheets of pivot tables and
then copies the data onto other sheets. I would like to know how I can
delete all of these sheets as they are not needed.

These sheets all have the standard new sheet name. I.e. Sheetxx

The VBA Code for this would be tremendous :)


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=508547

Delete Multiple sheets
 

Absolutely correct on the DisplayAlerts. Thanks for noticing.
--
HTH...

Jim Thomlinson


"GS" wrote:

Hi Jim,

You should be turning .DisplayAlerts back on (TRUE) in your ErrorHandler:
Regards, GS

"Jim Thomlinson" wrote:

This should do the trick. Put it in a standard module...

Sub DeleteSheets()
Dim wks As Worksheet
On Error GoTo ErrorHandler
Application.DisplayAlerts = False
For Each wks In Worksheets
If Left(wks.Name, 5) = "Sheet" Then wks.Delete
Next wks
ErrorHandler:
Application.DisplayAlerts = False
End Sub
--
HTH...

Jim Thomlinson


"moglione1" wrote:


Hi all,

I have a macro that creates upto about 15 sheets of pivot tables and
then copies the data onto other sheets. I would like to know how I can
delete all of these sheets as they are not needed.

These sheets all have the standard new sheet name. I.e. Sheetxx

The VBA Code for this would be tremendous :)


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=508547