| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Pivot Item Visible - Why so difficult
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Pivot Item Visible - Why so difficult
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Pivot Item Visible - Why so difficult
On Jan 3, 9:52*am, Debra Dalgleish 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table Pivot Item Visible - Why so difficult
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 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thanks, works, but why?
That's great but why did it work? I'm writing a course on VBA and my students
will likely ask what is the sort command and why is it necessary? TIA "Debra Dalgleish" 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 Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thanks, works, but why?
The sort setting is the one you can manually change in the Field
Settings, when you click the Advanced button. If it's manually or programmatically set to Manual, you shouldn't get the error when making pivot items visible. If it's set to Ascending or Descending, you'll get the error when you programmatically try to make an item visible. I don't know the reason why. headly wrote: That's great but why did it work? I'm writing a course on VBA and my students will likely ask what is the sort command and why is it necessary? TIA "Debra Dalgleish" 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thanks, works, but why?
Thank you so much; I hope you don't mind if I give you credit in my
courseware! I will also highly recommend your books on pivot stuff, you are far away too expert. Happy holidays, you rock! "Debra Dalgleish" wrote: The sort setting is the one you can manually change in the Field Settings, when you click the Advanced button. If it's manually or programmatically set to Manual, you shouldn't get the error when making pivot items visible. If it's set to Ascending or Descending, you'll get the error when you programmatically try to make an item visible. I don't know the reason why. headly wrote: That's great but why did it work? I'm writing a course on VBA and my students will likely ask what is the sort command and why is it necessary? TIA "Debra Dalgleish" 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thanks, works, but why?
You may want to share Debra's site with your students, too.
headly wrote: Thank you so much; I hope you don't mind if I give you credit in my courseware! I will also highly recommend your books on pivot stuff, you are far away too expert. Happy holidays, you rock! "Debra Dalgleish" wrote: The sort setting is the one you can manually change in the Field Settings, when you click the Advanced button. If it's manually or programmatically set to Manual, you shouldn't get the error when making pivot items visible. If it's set to Ascending or Descending, you'll get the error when you programmatically try to make an item visible. I don't know the reason why. headly wrote: That's great but why did it work? I'm writing a course on VBA and my students will likely ask what is the sort command and why is it necessary? TIA "Debra Dalgleish" 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Thanks, works, but why?
You're welcome! Thanks for letting me know that it helped.
headly wrote: Thank you so much; I hope you don't mind if I give you credit in my courseware! I will also highly recommend your books on pivot stuff, you are far away too expert. Happy holidays, you rock! "Debra Dalgleish" wrote: The sort setting is the one you can manually change in the Field Settings, when you click the Advanced button. If it's manually or programmatically set to Manual, you shouldn't get the error when making pivot items visible. If it's set to Ascending or Descending, you'll get the error when you programmatically try to make an item visible. I don't know the reason why. headly wrote: That's great but why did it work? I'm writing a course on VBA and my students will likely ask what is the sort command and why is it necessary? TIA "Debra Dalgleish" 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 Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table Calculated Item | Excel Discussion (Misc queries) | |||
| Pivot Table Calculated Item | Excel Discussion (Misc queries) | |||
| pivot table data item | Excel Programming | |||
| Visible Property Pivot Item | Excel Programming | |||
| Difficult Pivot Table Technical Question | Excel Programming | |||
Hybrid Mode
