Results 1 to 4 of 4

How to use pivottable report filter in code

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    How to use pivottable report filter in code

    I have a data table which is summarised using a pivot table. Some fields are excluded using the report filter manually and I want to do this in code after the user has entered a date range into 2 textboxes.
    In order to get a grasp of this I ran a macro which gave me the following code
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        ActiveSheet.PivotTables("PivotTable1").PivotFields("INV DATE").CurrentPage = _
            "(All)"
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("INV DATE")
            .PivotItems("(blank)").Visible = False
            .PivotItems("07/12/2017").Visible = False ***************
            .PivotItems("08/12/2017").Visible = False
            .PivotItems("11/12/2017").Visible = False
        End With
    End Sub
    When I try and run this macro I get an error report "Unable to get the PivotItems property of the PivotField Class" on the line marked *****
    When I check, all the pivotItems are in the list produced by the filter which has reset to "All"
    Where am I going wrong and how can I generalise the code.
    The attached workbook shows the pivottable. The data is not shown.
    John
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel VBA - PivotTable Report Filter -- Selecting All
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2014, 07:48 AM
  2. PivotTable: change multi report filters code
    By saudi_red_neck in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2014, 10:40 AM
  3. vba code for Pivot report filter
    By limlim in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-24-2012, 01:15 PM
  4. test if a Report Filter exists in a Pivottable
    By djblois1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-07-2012, 09:57 AM
  5. Replies: 1
    Last Post: 03-20-2012, 10:43 AM
  6. PivotTable Not refreshing when Report filter selection is changed
    By newbie11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-10-2009, 06:58 PM
  7. [SOLVED] Excel 2007 PivotTable Report Filter area not working
    By DaBus11 in forum Excel General
    Replies: 0
    Last Post: 07-26-2006, 09:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1