Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I delete multiple sheets in VBA... | Excel Programming | |||
Delete rows in multiple sheets without loop? | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Delete Rows from multiple Sheets. | Excel Programming |