If Pivot Table Exists
 

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

If Pivot Table Exists
 

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

--
HTH...

Jim Thomlinson


"hoppermr" wrote:

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

If Pivot Table Exists
 

Thanks for that but even with

Set ws = ActiveSheet

it doesn't work. Any other ideas?

ta

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"hoppermr" wrote:

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

If Pivot Table Exists
 

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


"hoppermr" a écrit dans le message de
news: ...
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

If Pivot Table Exists
 

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

--
HTH...

Jim Thomlinson


"hoppermr" wrote:

Thanks for that but even with

Set ws = ActiveSheet

it doesn't work. Any other ideas?

ta

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"hoppermr" wrote:

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