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

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

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

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" <hoppermr@discussions.microsoft.com> a écrit dans le message de
news: CC0C9CE5-5803-4DE3-BFBF-AF60845915F0@microsoft.com...
>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

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