Determine If Pivot Table Exists

[Guest]

Hey,

I need to loop through all the worksheets in a workbook and for each
worksheet with a pivot table I want to cleanup the pivot cache.

I've done this for workbooks where all the worksheets have a pivot table but
I'm having trouble doing this where some worksheets have a pivot table and
some don't. My guess is I need to determine if a worksheet has a pivot table
and if it does clean it up otherwise move on to the next spreadsheet.

I'm converting existing VBA code to VB.NET 2005 so an example using either
language would work for me.

Thanks for the help!

 

[Dave Peterson]

dim wks as worksheet
dim iCtr as long
for each wks in activeworkbook.worksheets
for iCtr = 1 to wks.pivottables.count
next ictr
next wks

Or if you just want to check...

if wks.pivottables.count > 0 then
'it has at least one pt
end if

 

[Guest]

Maybe something like this?:

'--------Start of Code-----------
Sub ListPvtTblSheets()
Dim shtWS As Worksheet
Dim pvtPT As PivotTable
Dim strMsg As String

strMsg = ""

For Each shtWS In ThisWorkbook.Worksheets
If shtWS.PivotTables.Count > 0 Then
strMsg = strMsg & shtWS.Name & vbCr
For Each pvtPT In shtWS.PivotTables
strMsg = strMsg & "-" & pvtPT.Name & vbCr
Next pvtPT
End If
Next shtWS

MsgBox strMsg
End Sub
'--------End of Code-----------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Dim sh as Worksheet, pt as PivotTable
for each sh in worksheets
if sh.PivotTables.count > 1 then
for each pt in sh.PivotTables


Next
end if
Next

 

[Guest]

Thanks for the help!

I was able to get this to work with a little trial error. Just what I needed.