Pivotitem 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 Stack pivotitems visible false all Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs unable to set the visible property of the pivotitem class excel 2010 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. Join unable to set the visible property of the pivotitem class excel 2007 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 .PivotItems("Germany").Visible
Pi.visible = True Error
= 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 Pi As PivotIte
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
Excel Vba Pivotitems Visible
about Stack Overflow the company Business Learn more about hiring developers or posting ads pivotitem orientation with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow excel vba loop through pivotitems is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why is this PivotItem.Visible call throwing a TypeMismatch error? up vote 1 down http://stackoverflow.com/questions/14387938/looping-through-report-filters-to-change-visibility-doesnt-work vote favorite This is baffling us. I have a standard pivot table with a report filter on it that allows multiple selection of items. I can get the selected items in the report filter with: Dim pi As PivotItem For Each pi In PivotTables("MyPivot").PivotFields("MyField").PivotItems If pi.Visible Then Debug.Print pi.Value End If Next Simple. My collegue has a standard pivot table with a report filter on it that allows multiple http://stackoverflow.com/questions/19424926/why-is-this-pivotitem-visible-call-throwing-a-typemismatch-error selection of items. He tries getting selected items in the report filter with the same code: Dim pi As PivotItem For Each pi In PivotTables("HisPivot").PivotFields("HisField").PivotItems If pi.Visible Then Debug.Print pi.Value End If Next And gets a Type Mismatch error on pi.Visible. We know that Visible is a property in pi, as after typing pi. the intellisense appears with all the PivotItem properties and methods (as you'd expect). We know that pi contains a valid PivotItem, as calling pi.Value prints the value correctly (removing the If/End If statements to just let it print the value regardless will print every item in the list). There is nothing 'special' about his report filter - it isn't a calculated field or anything like that. Most other properties of PivotItem also fail. Does anyone know why PivotItem would show this behaviour? The MSDN reference seems rather inadequate. excel-vba excel-2010 pivot-table share|improve this question edited Aug 23 at 16:44 iliketocode 2,78542045 asked Oct 17 '13 at 11:03 Kai 96621128 add a comment| 3 Answers 3 active oldest votes up vote 0 down vote accepted My collegue found the answer after much Googling in this post: For your information and for others with this problem - I have cracked it and done so simp
Forums Excel Questions VBA - Setting Pivot Item to Visible False Errors Results 1 to 10 of 10 VBA - Setting Pivot Item to Visible False ErrorsThis is a discussion on VBA - Setting Pivot Item to Visible False Errors within the Excel http://www.mrexcel.com/forum/excel-questions/518846-visual-basic-applications-setting-pivot-item-visible-false-errors.html Questions forums, part of the Question Forums category; I have a pivot table with a pivot field of Week Date. My vba tries to hide all of the ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg http://www.contextures.com/xlPivot03.html this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jan 4th, 2011,11:42 AM #1 cfoye130 Board Regular Join Date Aug 2008 Posts 84 unable to VBA - Setting Pivot Item to Visible False Errors I have a pivot table with a pivot field of Week Date. My vba tries to hide all of the week dates except for the current week date. It gets that current week date value from a cell in the sheet. It works fine and hides every date that is not the current week date until it gets to 1 from the end, and then errors with a 1004, unable unable to set to set visible class. Any ideas? Code is listed below. Thanks! Code: For Each w In ThisWorkbook.Worksheets For Each pt In w.PivotTables pt.RefreshTable pt.Update Next Next For Each w In ThisWorkbook.Worksheets For Each pt In w.PivotTables Set pf = pt.PivotFields("Week Date") pf.ClearAllFilters For Each pi In pf.PivotItems If pi = Worksheets("Control Sheet").Range("B10").Value Then 'pi.Visible = True Else pi.Visible = False End If Next pi Next Next I've stepped through the code and when it gets to the date it will error on, it recognizes that it is in fact the Else, then goes to pi.visible = False, but then errors when it tries to set the property. Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Jan 4th, 2011,11:53 AM #2 DonkeyOte MrExcel MVP Join Date Sep 2002 Location Suffolk, UK Posts 9,118 Re: VBA - Setting Pivot Item to Visible False Errors You would need one visible item at all times, no ? (It seems your code is hiding everything) In general - ensure a valid item is unhidden before then running a hide routine. Share Share this post on Digg Del.icio.us Technorati Twitter Does my a$$ look big in this picture ? Reply With Quote Jan 4th, 2011,11:55 AM #3 cfoye130 Board Regular Join Date Aug 2008 Posts 84 Re: VBA - Setting Pivot Item to Visible False Errors It is unhidden because I cleared the filter right? It sh
Hide Pivot Items Manually Show All Items -- Macro Hide All Items Except Last - Macro Show Items in Specific Field - Macro Hide Items in Specific Field - Macro Show Specific Item in Specific Field - Macro Show Specific Pivot Chart Items - Macro Hide Specific Pivot Chart Items - Macro Hide Pivot Subtotals = Macro Download the Sample File Show and Hide Pivot Items Manually Each field button has an arrow that opens a drop down list of items. To switch between Single Selection mode and Multiple Selection mode, click in the Select Multiple Items.checkbox, at the bottom of the list Show Pivot Items In Single Selection mode, click on any item, to see just that item, or click (All), to see all the items. Hide Pivot Items You can hide pivot items, but at least one pivot item must remain visible. You can't do a "Hide All", either manually, or with a macro. In Multiple Selection mode, click on any check mark, to clear a check box, and hide that item. Or, to show only a few items in a long list: Remove the check mark from the (All) checkbox, to remove all the check marks in the list Then, check at least one of the items in the list Click OK Show All Items - Macro Instead of selecting pivot items manually, you can use a macro to show or hide multiple fields. The following code will show all items in all row fields, in ALL pivot tables on the active sheet. To show column fields, change pt.RowFields to pt.ColumnFields. To show all fields, change pt.RowFields to pt.VisibleFields. Sub PivotShowItemAllVisible() 'pivot table tutorial by contextures.com 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of PivotItem class Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.RowFields pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems pi.Visible = True Next pi pf.AutoSort xlAscending, pf.SourceName Next pf Next pt Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Hide All Items Except Last - Macro Instead of hiding pivot items manually, you can use a macro to show or hide multiple items. Excel will not allow you to hide all the items thou