|
Using Code to show all pivot items
I have a pivot table called "Relationship" contained within the
worksheet "Relationship Table" which contains a column field called "Order No" where individual pivot items for this column can be hidden. I am trying to write code to automatically show all of the pivot items. When I use the following ShowAllItems command nothing happens. Why? ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No").ShowAllItems = True I have also tried to use the following statement in an array to set each item to visible but that causes me to get the "Run-time error '1004' Unable to set property of the Pivot Item class" error. What is really frustrating is that I can use this statement to set the pivot item visible to false but not to true. ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No")..PivotItems(i).Visible = True Any help given would be greatly appreciated. |
Using Code to show all pivot items
In general:
Sub ShowAllPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields For Each pi In pf.PivotItems pi.Visible = True Next pi Next pf Next pt End Sub For your specific example: Set pt = ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship") Set pf = pt.PivotFields("Order No") For Each pi In pf.PivotItems pi.Visible = True Next pi HTH, Bernie MS Excel MVP wrote in message ups.com... I have a pivot table called "Relationship" contained within the worksheet "Relationship Table" which contains a column field called "Order No" where individual pivot items for this column can be hidden. I am trying to write code to automatically show all of the pivot items. When I use the following ShowAllItems command nothing happens. Why? ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No").ShowAllItems = True I have also tried to use the following statement in an array to set each item to visible but that causes me to get the "Run-time error '1004' Unable to set property of the Pivot Item class" error. What is really frustrating is that I can use this statement to set the pivot item visible to false but not to true. ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No")..PivotItems(i).Visible = True Any help given would be greatly appreciated. |
Using Code to show all pivot items
Thanks Bernie
I tried your code but the pi.Visible = True statement still gives me the "Run-time '1004', Unable to set the Visible property of the PivotItem class". Still not sure why but thanks for the help. If there are any other suggestions I would be more then happy to give them a try. Cheers Will Bernie Deitrick wrote: In general: Sub ShowAllPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields For Each pi In pf.PivotItems pi.Visible = True Next pi Next pf Next pt End Sub For your specific example: Set pt = ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship") Set pf = pt.PivotFields("Order No") For Each pi In pf.PivotItems pi.Visible = True Next pi HTH, Bernie MS Excel MVP wrote in message ups.com... I have a pivot table called "Relationship" contained within the worksheet "Relationship Table" which contains a column field called "Order No" where individual pivot items for this column can be hidden. I am trying to write code to automatically show all of the pivot items. When I use the following ShowAllItems command nothing happens. Why? ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No").ShowAllItems = True I have also tried to use the following statement in an array to set each item to visible but that causes me to get the "Run-time error '1004' Unable to set property of the Pivot Item class" error. What is really frustrating is that I can use this statement to set the pivot item visible to false but not to true. ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No")..PivotItems(i).Visible = True Any help given would be greatly appreciated. |
Using Code to show all pivot items
Will,
Personally, I would copy the data table over to a new workbook and re-create the PT - very easy to do, the beauty of PTs. And then see if the code works or not. Often, trying out code on a workbook can cause corruption. HTH, Bernie MS Excel MVP wrote in message ups.com... Thanks Bernie I tried your code but the pi.Visible = True statement still gives me the "Run-time '1004', Unable to set the Visible property of the PivotItem class". Still not sure why but thanks for the help. If there are any other suggestions I would be more then happy to give them a try. Cheers Will Bernie Deitrick wrote: In general: Sub ShowAllPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields For Each pi In pf.PivotItems pi.Visible = True Next pi Next pf Next pt End Sub For your specific example: Set pt = ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship") Set pf = pt.PivotFields("Order No") For Each pi In pf.PivotItems pi.Visible = True Next pi HTH, Bernie MS Excel MVP wrote in message ups.com... I have a pivot table called "Relationship" contained within the worksheet "Relationship Table" which contains a column field called "Order No" where individual pivot items for this column can be hidden. I am trying to write code to automatically show all of the pivot items. When I use the following ShowAllItems command nothing happens. Why? ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No").ShowAllItems = True I have also tried to use the following statement in an array to set each item to visible but that causes me to get the "Run-time error '1004' Unable to set property of the Pivot Item class" error. What is really frustrating is that I can use this statement to set the pivot item visible to false but not to true. ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFields(" Order No")..PivotItems(i).Visible = True Any help given would be greatly appreciated. |
Using Code to show all pivot items
Thanks Debra
Setting the Sort to Manual fixed the problem with setting pivot item visible to true. It didn't fix the ShowAllItem command which appears to be the quickest way to do this but I can definitely use what you gave me. If you have any insight into the later issue that would be great, if not that's great too. Regards Will Debra Dalgleish wrote: To prevent the error, set the Sort for the field to Manual. You can do this in the code, for example: '================================== Sub PivotShowItemResetSort() 'For version 2000 -- show all items in field 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of PivotItem class 'returns sort order to previous setting Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim intASO As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.VisibleFields intASO = pf.AutoSortOrder pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems If pi.Visible < True Then pi.Visible = True End If Next pi pf.AutoSort intASO, pf.SourceName Next pf Next pt Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub '================================ wrote: Thanks Bernie I tried your code but the pi.Visible = True statement still gives me the "Run-time '1004', Unable to set the Visible property of the PivotItem class". Still not sure why but thanks for the help. If there are any other suggestions I would be more then happy to give them a try. Cheers Will Bernie Deitrick wrote: In general: Sub ShowAllPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields For Each pi In pf.PivotItems pi.Visible = True Next pi Next pf Next pt End Sub For your specific example: Set pt = ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship") Set pf = pt.PivotFields("Order No") For Each pi In pf.PivotItems pi.Visible = True Next pi HTH, Bernie MS Excel MVP wrote in message roups.com... I have a pivot table called "Relationship" contained within the worksheet "Relationship Table" which contains a column field called "Order No" where individual pivot items for this column can be hidden. I am trying to write code to automatically show all of the pivot items. When I use the following ShowAllItems command nothing happens. Why? ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotField s("Order No").ShowAllItems = True I have also tried to use the following statement in an array to set each item to visible but that causes me to get the "Run-time error '1004' Unable to set property of the Pivot Item class" error. What is really frustrating is that I can use this statement to set the pivot item visible to false but not to true. ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotField s("Order No")..PivotItems(i).Visible = True Any help given would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using Code to show all pivot items
The ShowAllItems property is like adding a check mark to the 'Show items
with no data' option. It's not the equivalent of checking (Show All) in the item dropdown. wrote: Thanks Debra Setting the Sort to Manual fixed the problem with setting pivot item visible to true. It didn't fix the ShowAllItem command which appears to be the quickest way to do this but I can definitely use what you gave me. If you have any insight into the later issue that would be great, if not that's great too. Regards Will Debra Dalgleish wrote: To prevent the error, set the Sort for the field to Manual. You can do this in the code, for example: '================================== Sub PivotShowItemResetSort() 'For version 2000 -- show all items in field 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of PivotItem class 'returns sort order to previous setting Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim intASO As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.VisibleFields intASO = pf.AutoSortOrder pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems If pi.Visible < True Then pi.Visible = True End If Next pi pf.AutoSort intASO, pf.SourceName Next pf Next pt Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub '================================ wrote: Thanks Bernie I tried your code but the pi.Visible = True statement still gives me the "Run-time '1004', Unable to set the Visible property of the PivotItem class". Still not sure why but thanks for the help. If there are any other suggestions I would be more then happy to give them a try. Cheers Will Bernie Deitrick wrote: In general: Sub ShowAllPivot() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem For Each pt In ActiveSheet.PivotTables For Each pf In pt.PivotFields For Each pi In pf.PivotItems pi.Visible = True Next pi Next pf Next pt End Sub For your specific example: Set pt = ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship") Set pf = pt.PivotFields("Order No") For Each pi In pf.PivotItems pi.Visible = True Next pi HTH, Bernie MS Excel MVP wrote in message egroups.com... I have a pivot table called "Relationship" contained within the worksheet "Relationship Table" which contains a column field called "Order No" where individual pivot items for this column can be hidden. I am trying to write code to automatically show all of the pivot items. When I use the following ShowAllItems command nothing happens. Why? ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFie lds("Order No").ShowAllItems = True I have also tried to use the following statement in an array to set each item to visible but that causes me to get the "Run-time error '1004' Unable to set property of the Pivot Item class" error. What is really frustrating is that I can use this statement to set the pivot item visible to false but not to true. ThisWorkbook.Worksheets("Relationship tables").PivotTables("Relationship").PivotFie lds("Order No")..PivotItems(i).Visible = True Any help given would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 06:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com