VBA delete Pivot table

[ziad alsayed]

dear all

how can i delete all pivot tables in a workbook

apprreciate any help.

 

[Zack Barresse]

Hello there,

You could use code like this...



<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> DeleteAllPivotTablesInWorkbook()<br>    <SPAN style="color:#00007F">Dim</SPAN> WB <SPAN style="color:#00007F">As</SPAN> Workbook, WS <SPAN style="color:#00007F">As</SPAN> Worksheet, PT <SPAN style="color:#00007F">As</SPAN> PivotTable<br>    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "There is no active workbook!", vbExclamation, "ERROR!"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> MsgBox("Delete ALL pivot tables in the active workbook?", _<br>        vbYesNo + vbDefaultButton2, "DELETE ALL?") = vbNo <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> WS <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> PT <SPAN style="color:#00007F">In</SPAN> WS.PivotTables<br>            WS.Range(PT.TableRange2.Address).Delete Shift:=xlUp<br>        <SPAN style="color:#00007F">Next</SPAN> PT<br>    <SPAN style="color:#00007F">Next</SPAN> WS<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Please note there is NO UNDO for this. Save a copy of your workbook first, just in case.

HTH

 

[ziad alsayed]

thanks zack , it is working.

 

[mariog]

The above code works great, but just fyi you could also use

For Each pt In WS.PivotTables
    pt.TableRange2.Clear
Next pt

Instead of the .Address thing. At least that's how I do it!

 

[Zack Barresse]

True, and Clear would probably be better, don't have to worry about actually deleting ranges. I'm always going on about not inserting and deleting, and there I go posting to delete... duh. Thanks mariog.

 

[ziad alsayed]

thanks mariog. this is great.

 

[mariog]

Sure thing! Glad I could finally help someoune out and not the other way around

 

[kingrave]

Hi sir

What if I want to delete only specific Pivot table in my active sheet.

 

[Domenic]

Hi sir

What if I want to delete only specific Pivot table in my active sheet.

Try...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] DeleteSpecificPivotTables()

    [color=darkblue]Dim[/color] Wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] PT [color=darkblue]As[/color] PivotTable
    
    [color=darkblue]If[/color] ActiveWorkbook [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        MsgBox "There is no active workbook!", vbExclamation, "ERROR!"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Wks [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        [color=darkblue]For[/color] [color=darkblue]Each[/color] PT [color=darkblue]In[/color] Wks.PivotTables
            [color=darkblue]Select[/color] [color=darkblue]Case[/color] PT.Name
                [color=darkblue]Case[/color] "PivotTable1", "PivotTable2", "PivotTable3"
                    PT.TableRange2.Clear
            [color=darkblue]End[/color] [color=darkblue]Select[/color]
        [color=darkblue]Next[/color] PT
    [color=darkblue]Next[/color] Wks
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Change the specified pivot tables, accordingly.

Hope this helps!