Delete Multiple sheets

[moglione1]

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

[Jim Thomlinson]

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
>
>

[GS]

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
>
>

[GS]

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
> >
> >

[Jim Thomlinson]

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
> > >
> > >