Pivotitem Visible Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this
Vba Unable To Set The Visible Property Of The Pivotitem Class
site About Us Learn more about Stack Overflow the company Business Learn more unable to set the visible property of the pivotitem class excel 2010 about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x unable to set the visible property of the pivotitem class excel 2007 Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Unable to
Pivotitem Orientation
set the Visible property of the PivotItem class (VBA) up vote 0 down vote favorite I am trying to manipulate an Excel 2007 Pivot Table trough VBA so I can loop trough the categories of the pivot table, set all to invisible but one, save the sheet as pdf and continue to the next category. For this I use the following piece of code.
Unable To Set The Visible Property Of The Pivotitem Class Excel 2013
Dim pf As PivotField Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME") Dim pi as PivotItem For Each pi In pf.PivotItems If pi.Visible = False Then pi.Visible = True 'Error here End If Dim pi2 As PivotItem For Each pi2 In pf.PivotItems If pi2 <> pi Then pi2.Visible = False End If Next pi2 'Saving to PDF goes here Next pi The loop seems to be working the first time. Every category gets deselected but the first and it outputs a nice PDF file. The next time it enters the loop however it gives an 'Unable to set the Visible property of the PivotItem class' error at the indicated line. I am aware of the fact that in a pivot table there has to be at least one item selected but that is not the problem here as I am trying to set the visibility to TRUE instead of FALSE. I tried fixing it by putting a check around it as maybe you are not allowed to set an already visible PivotItem to visible but that did not seem to work. Any help would be very much appreciated! excel vba excel-vba pivot-table share|improve this questi
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this pivotitems visible false all site About Us Learn more about Stack Overflow the company Business Learn more
Pi.visible = True Error
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss pivotitems visible false error Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up error 1004 excel http://stackoverflow.com/questions/11468705/unable-to-set-the-visible-property-of-the-pivotitem-class-vba 2010: unable to set the visible property of the pivotitem class up vote 0 down vote favorite I cannot make my code working because of the so famous errore above. I was unable to find a solution reading previous posts, so hope anyone can help me. I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an http://stackoverflow.com/questions/24656654/error-1004-excel-2010-unable-to-set-the-visible-property-of-the-pivotitem-class array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly. Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line pvtItm.Visible = False Many thanks! This is my code: Sub ptFilterOffnet() Dim PvtTbl As PivotTable Dim pvtItm As PivotItem Dim pvtFld As String Dim listItem As Variant Dim listOffnet As Variant Dim lastRow As Integer Dim myws As String pvtFld = "CATEGORY_DESCRIPTION" myws = "GESTIONE_ADMIN" resetSlicers 'function resetting all slicers connected to pivot tables (all items ON) ' Populate the array with some data in a specific range lastRow = Sheets(myws).Cells(Rows.Count, 10).End(xlUp).Row listOffnet = populateArray(myws, 3, lastRow, 10, 10) ' All OFF but items within listOffnet For Each PvtTbl In Sheets("P1").PivotTables For Each pvtItm In PvtTbl.PivotFields(pvtFld).PivotItems For Each listItem In listOffnet If pvtItm <> listItem Then pvtItm.Visible = False End If Next Next Next End Sub excel pivot visible share|improve this question asked Jul 9 '14 at 14:37 user3820865 1 add a comment| 1 Answer 1 active oldest vot
as I un-select one item and then select another item: With ActiveSheet.PivotTables("PivotTable1").PivotFields("Env") .PivotItems("1").Visible = False .PivotItems("42").Visible = True End With But when I then try to run (playback) the macro, I get an error 1004. I still get the error if I http://www.pcreview.co.uk/threads/error-1004-with-pivotitems-visible.2246575/ change the order and put True first, then False. What am I missing? Thanks, Charlie Rowe Guest, Nov 18, 2005 #1 Advertisements Debra Dalgleish Guest To prevent the error, set the Sort for the field to Manual. For example: Dim intASO As Integer With ActiveSheet.PivotTables("PivotTable2").PivotFields("Employee") intASO = .AutoSortOrder .AutoSort xlManual, .SourceName .PivotItems("1").Visible = False .PivotItems("42").Visible = True .AutoSort intASO, .SourceName End With Charlie Rowe wrote: > I record this macro as I un-select one item and then unable to select another item: > With ActiveSheet.PivotTables("PivotTable1").PivotFields("Env") > .PivotItems("1").Visible = False > .PivotItems("42").Visible = True > End With > But when I then try to run (playback) the macro, I get an error 1004. > I still get the error if I change the order and put True first, then False. > > What am I missing? > > Thanks, > Charlie Rowe > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Debra Dalgleish, Nov 18, unable to set 2005 #2 Advertisements Guest Guest Debra, WOW, thank you so much; I would have never figured that out. How did you ever figure it out? Charlie Rowe "Debra Dalgleish" wrote: > To prevent the error, set the Sort for the field to Manual. For example: > > Dim intASO As Integer > With ActiveSheet.PivotTables("PivotTable2").PivotFields("Employee") > intASO = .AutoSortOrder > .AutoSort xlManual, .SourceName > .PivotItems("1").Visible = False > .PivotItems("42").Visible = True > .AutoSort intASO, .SourceName > End With > > Charlie Rowe wrote: > > I record this macro as I un-select one item and then select another item: > > With ActiveSheet.PivotTables("PivotTable1").PivotFields("Env") > > .PivotItems("1").Visible = False > > .PivotItems("42").Visible = True > > End With > > But when I then try to run (playback) the macro, I get an error 1004. > > I still get the error if I change the order and put True first, then False. > > > > What am I missing? > > > > Thanks, > > Charlie Rowe > > > > > > > -- > Debra Dalgleish > Excel FAQ, Tips & Book List > http://www.contextures.com/tiptech.html > > Guest, Nov 18, 2005 #3 Advertisements Show Ignored Content Want to reply to this thread or ask your own question? It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and th