• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

Excel Pivot Table Filters using VBA

My data will be updated everyday and when the pivot table is refreshed I want to set the Filter "AreaDescription" to a specific value and I want to set the row "Week" to a range of values.  These values will all be based on values in the cells on a different sheet in the workbook.  Below is the code I created, but it is getting an error on both the filter commands.

Sub PivotDefault()
    Dim PvtTbl As PivotTable
    Dim WeekStart As Integer
    Dim WeekEnd As Integer
    Dim Area As String
    
    Set PvtTbl = Worksheets("PivotTable").PivotTables("PivotByArea")
    
    PvtTbl.ClearAllFilters
    
    WeekStart = Worksheets("Misc").Range("D2")
    WeekEnd = Worksheets("Misc").Range("D3")
    Area = Worksheets("Misc").Range("G1")
    
    PvtTbl.PivotFields("AreaDescription").PivotFilters.Add Type:=xlCaptionEquals, _
        Value1:=Area
    
    PvtTbl.PivotFields("Week").PivotFilters.Add Type:=xlValueIsBetween, _
        Value1:=WeekStart, Value2:=WeekEnd
End Sub

Open in new window


Thank you in Advanced!
0
jmkbrown
Asked:
jmkbrown
  • 4
  • 2
1 Solution
 
jmkbrownAuthor Commented:
I changed the code and now I am not getting any errors, but when the code applies the week filter I don't get any records to appear.  When I manually apply the filter it works just fine.  Here is the new code.

Sub PivotDefault()
    Dim PvtTbl As PivotTable
    Dim WeekStart As String
    Dim WeekEnd As String
    Dim Area As String
    
    Set PvtTbl = Worksheets("PivotTable").PivotTables("PivotByArea")
    
    PvtTbl.ClearAllFilters
    
    WeekStart = Worksheets("Misc").Range("E2")
    WeekEnd = Worksheets("Misc").Range("E3")
    Area = Worksheets("Misc").Range("G1")
    
    Sheets("PivotTable").Activate
    
    ActiveSheet.PivotTables("PivotByArea").PivotFields("Week").PivotFilters.Add _
        Type:=xlCaptionIsBetween, Value1:=WeekStart, Value2:=WeekEnd
        
    ActiveSheet.PivotTables("PivotByArea").PivotFields("AreaDescription"). _
        CurrentPage = Area
    
End Sub

Open in new window

0
 
Rgonzo1971Commented:
Could you send a dummy?
0
 
Saurabh Singh TeotiaCommented:
I'm Assuming your week start and week end dates are date.. and now when you are applying filter on those dates..it need to be in the same format and thats probably it's not pulling you the data when you apply the filter through macro..

Check the dates format and convert that week start and week end in the same format..

Assuming your dates are in YYYY-MM-DD format so it will look something like this...

WeekStart = format(Worksheets("Misc").Range("D2").value,"YYYY-MM-DD")
    WeekEnd = FORMAT(Worksheets("Misc").Range("D3").VALUE, "YYYY-MM-DD") 

Open in new window

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
jmkbrownAuthor Commented:
Attached is a sample of the spreadsheet.
Sample.xlsm
0
 
Saurabh Singh TeotiaCommented:
Their you go..try this..This will do what you are looking for...

Saurabh...
Sample.xlsm
0
 
jmkbrownAuthor Commented:
This worked perfectly!  Thank you very much!
0
 
jmkbrownAuthor Commented:
It worked!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now