If Pivot Table Exists, Delete on Close

[ryguy7272]

I am wondering if there is some simple code that I can run that will
automatically delete all Pivot tables in a Workbook, upon close of the
Workbook, or delete all sheets that contain Pivot Tables, upon close of the
Workbook. I have an Excel file on our firm’s LAN, which contains a lit of
useful data for several VPs, who go into the file to check certain items, and
make a few updates. I would like to give them a way to create Pivot Tables
on the fly (I already have the code for this) but I don’t want them to be
able to save the Pivot Tables because I surmise it will quickly bloat to un
unmanageable size and the performance (over the LAN) will slow considerably.
Again, I just want to save a few changes that some of the VPs will make, in
their forecasting, and then automatically delete any and all Pivot Tables.

I suspect it will look something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘If Pivot Table > 1 Then
‘Delete Pivot Table
‘End if
End Sub


Thanks in advance!!
Ryan---

 

[Debra Dalgleish]

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.TableRange2.Clear
Next pt
Next ws

 

[ryguy7272]

This seems to have worked very nicely. I have never used 'TableRange2'
before. What is this??? Please respond!!! I googled it and didn't find a
whole lot of information on 'TableRange2'...

Thanks for the help!
Ryan---

 

[Debra Dalgleish]

That's the pivot table range, including the page fields.
TableRange1 does not include the page fields.

 

[Sunil Kumar Bairam]

Hello Debra,

Thanks for your simple and best answer, It helped me a lot