Pivotitems.visible Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the
Vba Unable To Set The Visible Property Of The Pivotitem Class
workings and policies of this site About Us Learn more about pivotitems visible false all Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Unable To Set The Visible Property Of The Pivotitem Class Excel 2010
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. pi.visible = true error Join them; it only takes a minute: Sign up Looping through report filters to change visibility doesn't work up vote 4 down vote favorite I'm trying to select one report filter, in this case Canada. That means the rest must be made invisible. This code works without issue: Public Sub FilterPivotTable() With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY") .PivotItems("Canada").Visible = True .PivotItems("USA").Visible = False excel vba pivotitems visible .PivotItems("Germany").Visible = False .PivotItems("France").Visible = False End With End Sub However, I'm trying to prepare for when we add other countries to our "Epidemiology" pivot table, so I tried to have a for loop. This code doesn't work: With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY") .PivotItems("Canada").Visible = True For Each Pi In .PivotItems If Pi.Value = "CANADA" Then Pi.Visible = True Else Pi.Visible = False End If Next Pi End With It gives me an error on the Pi.Visible = False line. The error that I get is Run-time error '1004': Unable to set the Visible property of the PivotItem class Why doesn't it work inside a for loop?! Frustratingly, all the examples I find online use similar syntax. (Some use an index, but I tried that and got the same error.) excel vba excel-vba pivot-table share|improve this question edited Aug 18 '15 at 10:22 Jean-Francois Corbett 24k1679114 asked Jan 17 '13 at 20:55 user1507455 1832310 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote Is this what you are trying? Sub Sample() Dim
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 site About Us Learn more about Stack Overflow the company Business Learn more
Unable To Set The Visible Property Of The Pivotitem Class Excel 2007
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users excel vba loop through pivotitems Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each
Pivotitem Orientation
other. Join them; it only takes a minute: Sign up unable to get the visible property of the pivotitem class up vote 1 down vote favorite I have two sets of code. For some reason, in the first http://stackoverflow.com/questions/14387938/looping-through-report-filters-to-change-visibility-doesnt-work one I get an error, and in the second one I don't. 1: Dim pi As PivotItem Dim pf As PivotField Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school") For Each pi In pf.PivotItems If pi = "(leeg)" Then pi.Visible = False Else pi.Visible = True 'ERROR HERE End If Next pi 2: Dim pi As PivotItem Dim pf As PivotField Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("naam locatie") For Each pi In pf.PivotItems If InStr(pi, "BSO") Then pi.Visible = http://stackoverflow.com/questions/32903522/unable-to-get-the-visible-property-of-the-pivotitem-class True Else pi.Visible = False End If Next pi I get the error: "unable to get the visible property of the pivotitem class" I read I should solve this the following: This is due to the Pivot table using the cached pivot items instead of the current one. Make sure the table does not retain any old items. To do so, right click on your pivot table, click on Data tab and set "Number of itesm to retain per field" to "None". The code to do so in VBA is: Dim pt As PivotTable pt.PivotCache.MissingItemsLimit = xlMissingItemsNone I tried to add this line of code in two ways: Dim pi As PivotItem Dim pf As PivotField Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school") pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '1st TRY For Each pi In pf.PivotItems pt.PivotCache.MissingItemsLimit = xlMissingItemsNone '2nd TRY If pi = "(leeg)" Then pi.Visible = False Else pi.Visible = True End If Next pi This doesn't seem to solve my problem. excel vba excel-vba pivot-table pivotitem share|improve this question edited Oct 2 '15 at 9:49 R3uK 7,13541448 asked Oct 2 '15 at 8:44 Grafit 344110 Started explanation in comments, but it is too long, starting an answer! ;) Just a quick question : Do you have multiples fields in your rows/columns fields? –R3uK Oct 2 '15 at 9:00 Thanks for helping! Actually, I don't. There's one
Forums Excel Questions PivotItems.Visible = TRUE/FALSE : Run-time error 1004 - Application defined or object defined error Page 1 http://www.mrexcel.com/forum/excel-questions/784849-pivotitems-visible-%3D-true-false-run-time-error-1004-application-defined-object-defined-error.html of 2 12 Last Jump to page: Results 1 to 10 of 11 2Likes Top All This Page PivotItems.Visible = TRUE/FALSE : Run-time error 1004 - Application defined or object defined errorThis is a discussion on PivotItems.Visible = TRUE/FALSE : Run-time error 1004 - Application defined or object defined error within the Excel Questions forums, part unable to of the Question Forums category; Hi everybody, I'm trying to filter my pivot table ("PivotTable2") which i create from a txt file. The filter consist ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to unable to set Hybrid Mode Switch to Threaded Mode Jun 16th, 2014,12:20 PM #1 Massawi_99 New Member Join Date Jun 2014 Posts 6 PivotItems.Visible = TRUE/FALSE : Run-time error 1004 - Application defined or object defined error Hi everybody, I'm trying to filter my pivot table ("PivotTable2") which i create from a txt file. The filter consist on keeping one PivotItem in the PivotField ( PivotField("Asset")). Here is the code I made : Code: Sub report() Dim m_PT_Table As PivotTable Dim m_PTField As PivotField Dim m_PTItem As PivotItem Set m_PT_Table = Worksheets("Breachs").PivotTables("PivotTable2") Set m_PTField = m_PT_Table.PivotFields("[Improved Total BacktestResults].[Asset].[Asset]") For Each m_PTItem In m_PTField.PivotItems If m_PTItem.Caption = Sheets("Ratio").Range("K" & 10).Value Then m_PTItem.Visible = True Else m_PTItem.Visible = False End If Next m_PTItem End Sub When running this Sub, I got a run-time error 1004, Application defined or object defined error, at the bold lines. Is there any ideas to fix this issue. Thanks in advance ps : This is my first post in the forum...Hope to find some