If Pivot Table Exists

  • Thread starter Thread starter Guest
  • Start date Start date
G

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