Pivotitems 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 site About Us Learn more about Stack Overflow the
Vba Unable To Set The Visible Property Of The Pivotitem Class
company Business Learn more about hiring developers or posting ads with us Stack Overflow unable to set the visible property of the pivotitem class excel 2010 Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 unable to set the visible property of the pivotitem class excel 2007 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Unable to set the Visible property of the PivotItem class (VBA) up vote 0 down vote favorite I am
Pivotitem Orientation
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. 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
Unable To Set The Visible Property Of The Pivotitem Class Excel 2013
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 question edited Jul 13 '12 at 16:41 Siddharth Rout 91.5k11102146 asked Jul 13 '12 at 10:28 Stijnvdk 833313 If you comment out the section that creates the PDF, does it work without error? –Dick Kusleika Jul 13 '12 at 13:46 What version of Excel are you using? –RBarryYou
Forums Excel Questions PivotItems.Visible = TRUE/FALSE : Run-time error 1004 - Application defined or object defined error Page 1 of 2 pivotitems visible false all 12 Last Jump to page: Results 1 to 10 of 11
Pi.visible = True Error
2Likes Top All This Page PivotItems.Visible = TRUE/FALSE : Run-time error 1004 - Application defined or object pivotitems visible false error 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 of the http://stackoverflow.com/questions/11468705/unable-to-set-the-visible-property-of-the-pivotitem-class-vba 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 Hybrid Mode Switch to http://www.mrexcel.com/forum/excel-questions/784849-pivotitems-visible-%3D-true-false-run-time-error-1004-application-defined-object-defined-error.html 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 answers :D Share Share this post on Digg De
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, http://www.pcreview.co.uk/threads/error-1004-with-pivotitems-visible.2246575/ 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 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 unable to .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 unable to set 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, 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, > >