From: headly on
A recorded macro does this code

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
.PivotItems("Aniseed Syrup").Visible = True
End With

Yet when i try the comand in the immediate window i get run time error 1004
unable to set the visible property of the pivotitem class

Am i missing a reference? As i type pivot code, i don't get any code hints.
is this a late binding problem?
From: Debra Dalgleish on
Add code to set the sort to manual, and that should prevent the error:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
.AutoSort xlManual, .SourceName
.PivotItems("Aniseed Syrup").Visible = True
.AutoSort xlAscending, .SourceName
End With


headly wrote:
> A recorded macro does this code
>
> With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
> .PivotItems("Aniseed Syrup").Visible = True
> End With
>
> Yet when i try the comand in the immediate window i get run time error 1004
> unable to set the visible property of the pivotitem class
>
> Am i missing a reference? As i type pivot code, i don't get any code hints.
> is this a late binding problem?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

From: Joe on
On Jan 3, 9:52 am, Debra Dalgleish <d...(a)contexturesXSPAM.com> wrote:
> Add code to set the sort to manual, and that should prevent the error:
>
> With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
>      .AutoSort xlManual, .SourceName
>         .PivotItems("Aniseed Syrup").Visible = True
>      .AutoSort xlAscending, .SourceName
> End With
>
> headly wrote:
> > A recorded macro does this code
>
> >     With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
> >         .PivotItems("Aniseed Syrup").Visible = True
> >     End With
>
> > Yet when i try the comand in the immediate window i get run time error 1004
> > unable to set the visible property of the pivotitem class
>
> > Am i missing a reference? As i type pivot code, i don't get any code hints.
> > is this a late binding problem?
>
> --
> Debra Dalgleish
> Contextureshttp://www.contextures.com/tiptech.html



Just a followup Qn on Pivot Table.

I want to refresh the Pivot table automaticall and I use the following
code.
D7 is the first cell in that Table.

Range("D7").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

It works fine most of the time, but show some error sometimes.
Any idea why? or Is there any better way of achieving the same?

Thanks a lot
Joe
From: Debra Dalgleish on
You don't need to select a cell in the pivot table, so you could delete
that line of code.
What error message do you get?

Joe wrote:
> On Jan 3, 9:52 am, Debra Dalgleish <d...(a)contexturesXSPAM.com> wrote:
>
>>Add code to set the sort to manual, and that should prevent the error:
>>
>>With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
>> .AutoSort xlManual, .SourceName
>> .PivotItems("Aniseed Syrup").Visible = True
>> .AutoSort xlAscending, .SourceName
>>End With
>>
>>headly wrote:
>>
>>>A recorded macro does this code
>>
>>> With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product Name")
>>> .PivotItems("Aniseed Syrup").Visible = True
>>> End With
>>
>>>Yet when i try the comand in the immediate window i get run time error 1004
>>>unable to set the visible property of the pivotitem class
>>
>>>Am i missing a reference? As i type pivot code, i don't get any code hints.
>>>is this a late binding problem?
>>
>>--
>>Debra Dalgleish
>>Contextureshttp://www.contextures.com/tiptech.html
>
>
>
>
> Just a followup Qn on Pivot Table.
>
> I want to refresh the Pivot table automaticall and I use the following
> code.
> D7 is the first cell in that Table.
>
> Range("D7").Select
> ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
>
> It works fine most of the time, but show some error sometimes.
> Any idea why? or Is there any better way of achieving the same?
>
> Thanks a lot
> Joe


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html