CODEWITHSUNDEEP
excelexcel-2007vba
L42
L42

Reputation: 19727

How to better update pivot table data filter?

I'm using this code below to update pivot table filter.
It only filters items under date field equal to todays date.
It is workning fine but is there a faster way to do this?

Dim wsPivot1 as Worksheet
Dim pt as PivotTable
Dim pt_item as PivotItem

Set wsPivot1 = Thisworkbook.Sheets("Pivot")

For Each pt In wsPivot1.PivotTables
    pt.RefreshTable
    If DatePart("h", Now()) < 6 Then
        date_stamp = Format(Now() - 1, "m/d/yyyy")
    Else
        date_stamp = Format(Now(), "m/d/yyyy")
    End If
    If pt.Name = "PivotTable1" Then
        pt.PivotFields("Date_Adj").ClearAllFilters
        For Each pt_item In pt.PivotFields("Date_Adj").PivotItems
            Select Case pt_item.Name
            Case date_stamp
                pt_item.Visible = True
            Case Else
                pt_item.Visible = False
            End Select
        Next pt_item
    End If
Next pt

Any help would be much appreciated. Thanks.

Upvotes: 1

Views: 4681

Answers (2)

L42
L42

Reputation: 19727

This is the method suggested by Jaycal.
Suppose you have a Pivot table in "Pivot" Sheet with filter in Range("A2").

This code works:

Dim wsPivot1 as Worksheet
Dim cel as Range


Set wsPivot1 = Thisworkbook.Sheets("Pivot")
Set cel = wsPivot1.Range("A2")

cel.PivotField.PivotFilters.Add xlCaptionEquals, "Date_Adj", date_stamp

It has one limitation though.
It will work on filters in Row and Column labels only.
Not on Report Filters.

Edit1: Use this if your working on Report Filters.

Dim pt As PivotTable

For Each pt in wsPivot1
    pt.RefreshTable
    pt.PivotFields("Date_Adj").ClearAllFilters
    pt.PivotFields("Date_Adj").CurrentPage = date_stamp
Next

Upvotes: 1

Jaycal
Jaycal

Reputation: 2087

Since it seems like you only want to show one date and hide the rest, you can create a custom filter for that date. Replace your for loop, i.e.

For Each pt_item In pt.PivotFields("Date_Adj").PivotItems
    Select Case pt_item.Name
        Case date_stamp
            pt_item.Visible = True
        Case Else
            pt_item.Visible = False
    End Select
Next pt_item

With this line

pt.PivotFields("Date_Adj").PivotFilters.Add _
    Type:=xlSpecificDate, Value1:=date_stamp

Note that the above code will only work if the values in your "Date_Adj" column are stored as dates. If they are not, you'll need to use this line of code instead

pt.PivotFields("Date_Adj").PivotFilters.Add _
    Type:=xlCaptionEquals, Value1:=date_stamp

EXAMPLE Understanding that my sample data may be severly oversimplified, this is what I used to test my solution (Note: Sample created using Excel 2007).

Sample Data

"Ship Date" is an entered date; "Date_Adj" is just the left adjacent cell + 1 (e.g. =B2+1).

Then I created the Pivot table and ran the code as you'll see in the image below

Pivot Table and Macro

Upvotes: 2

Related Questions