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

--
RyGuy

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 wrote:
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 firms 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 dont 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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

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

--
RyGuy


"Debra Dalgleish" wrote:

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 wrote:
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 firms 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 dont 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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

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

ryguy7272 wrote:
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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Hello Debra,

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