If Pivot Table Exists, Delete on Close
 

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

If Pivot Table Exists, Delete on Close
 

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

If Pivot Table Exists, Delete on Close
 

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

If Pivot Table Exists, Delete on Close
 

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

If Pivot Table Exists, Delete on Close
 

Hello Debra,

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