If Pivot Table Exists

[Guest]

I am looking for VBA code that simply returns a boolean value based on
whether a pivot table exists in a worksheet. Code I have come up with is
below:-

Dim ws as Worksheet
Dim PivotReport = PivotTable
If ws.PivotTables("PivotTable3") Is Nothing = False Then 'ie the
pivot table exists
Set PivotReport = ws.PivotTables("PivotTable3")
End If

When I run this I get a "1001 object defined run time error". It appears
the "Is Nothing" method is not right here. Is there an "Exists" or other
like function for determining the existence of a pivot table in a Worksheet?

Thanks Team

 

[Guest]

You do not specify what ws is.

Dim ws as Worksheet
Dim PivotReport as PivotTable

set ws = activesheet

If not ws.PivotTables("PivotTable3") Is Nothing Then
Set PivotReport = ws.PivotTables("PivotTable3")
End If

 

[Guest]

Thanks for that but even with

Set ws = ActiveSheet

it doesn't work. Any other ideas?

ta

 

[papou]

Hello
Amend your code as follows:
Dim ws as Worksheet
Dim PivotReport As PivotTable
If Not ws.PivotTables("PivotTable3") Is Nothing Then
Set PivotReport = ws.PivotTables("PivotTable3")

HTH
Cordially
Pascal

 

[Guest]

Dim ws as Worksheet
Dim PivotReport as PivotTable

set ws = activesheet
On error resume next
Set PivotReport = ws.PivotTables("PivotTable3")
On error Goto 0
if not pivotreport is nothing then

End If