+ Reply to Thread
Results 1 to 3 of 3

Pivot Table Filter Macro Not working

Hybrid View

  1. #1
    Registered User
    Join Date
    07-04-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    17

    Pivot Table Filter Macro Not working

    I am trying to speed up a pivot table filter of over 15000 items.

    I have been advised to add the code line "pt.ManualUpdate=True" which is now preventing it from running at all - Runtime Error 91, Object variable or With block variable not set.

    Has anyone got any suggestions of how to fix it but get the filter to run quickly, without any modification it takes around 4 mins to filter the report.

    Sub openfilter()
    '
    ' openfile Macro
    '
        Dim Pf As PivotField
        Dim Pi As PivotItem
        Dim pt As PivotTable
            
        Sheets("Mile").Select
        ActiveWindow.SmallScroll Down:=-21
        ActiveWorkbook.ShowPivotTableFieldList = False
        ActiveWindow.SmallScroll Down:=-270
        Application.ScreenUpdating = False
        'Application.Calculation = xlManual
        'ActiveSheet.PivotTables("PivotTable3").ManualUpdate = True
    
    
        'Application.DisplayAlerts = False
        
           
        Set Pf = ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
    'Fault on the next line...
    pt.ManualUpdate = True
    
       
        'Pf.ClearAllFilters
       
        With Pf
           For Each Pi In .PivotItems
               If Not (Pi.Caption Like "Dave") Then
                    Pi.Visible = False
    
               End If
           Next Pi
         End With
         
    pt.ManualUpdate = False
        
    Application.ScreenUpdating = True
    
    End Sub

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Pivot Table Filter Macro Not working

    While you have declared pt as a Pivottable object, you have not set pt to be the pivot table in your sheet.

    Try

    Set pt = ActiveSheet.PivotTables("PivotTable3")
    after you select the sheet.
    Martin

  3. #3
    Registered User
    Join Date
    07-04-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Pivot Table Filter Macro Not working

    Solved. Thanks MRice

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro that would filter pivot table top 10 items
    By weecha in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2013, 01:28 PM
  2. VBA Pivot Table Filter Macro
    By bobo1000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2012, 05:54 AM
  3. Macro to filter Pivot Table by range
    By dharmaskibum in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-25-2012, 04:21 PM
  4. Macro Help Needed - Link Pivot Table Filter to Regular Table AutoFilter
    By ycx1129 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-11-2011, 12:54 PM
  5. Macro to filter Pivot not working
    By Startrekfive in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2011, 04:20 AM

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