Excel VBA to filter Pivot Table and Pivot Chart for previous day - Pivot Filter Field

Question

I've searched everywhere on google (and here) and have tried various combinations of code from all kinds of sources, including this site, Excelguru, excelexperts, ozgrid, dedicatedexcel, excelcampus, spreadsheetguru, etc..... so on and so forth...

Here is my problem, every bit of code, every alteration, every type, doesnt work. I'm using Office 360 at my work site (up to date), so it's excel 2016 and VBA 7.1.

What I'm looking to do is automate our end of shift reports. Here's the process:

We enter data into an excel sheet (Log) every hour. At the end of the day, at 5:00 AM, we save and close that log, open another excel sheet that IMPORTS the data into power pivot, and displays it on a PivotTable (formatting for printing for our bosses), and we choose the filter for the previous date using the filter drop down, and print it. We do this with three (3) reports: 2 PivotTables, and 1 PivotChart. Power Pivot imports ALL of the data from the Log sheet to reformat it for printing.

I've successfully managed to get and rewrite the code (beginner at this) for the automation process of: auto saving the log, closing the log, opening the Report workbook, refreshing the data, and printing the data, then closing the report. The only part im now missing is the auto-filtering.

The code i've tried is vast, but here's an example of what i've tried recently (i've erased and re-copied so many codes...)

Sub Filter_PivotField()
'Description: Filter a pivot table or slicer for a specific date or period
'Source: excelcampus.com/vba/filter-pivot-table-slicer-recent-date-period

Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotFields

    'Set the variables
    sSheetName = "EOS Report"
    sPivotName = "PivotTable1"
    sFieldName = "Date"
    sFilterCrit = "xlDateYesterday"
    'sFilterCrit = ThisWorkbook.Worksheets("EOS Report").Range("O1").Value

    With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
        'Clear all filter of the pivotfield
        .ClearAllFilters

        'Loop through pivot items of the pivot field
        'Hide or filter out items that do not match the criteria
        For Each pi In .PivotFields
            If pi.Name  sFilterCrit Then
                pi.Visible = False
            End If
        Next pi

    End With

End Sub

To no avail....

When i record a macro doing the manual filter, i get this:

Sub manualfilter()
'
' manualfilter Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Bi-Hourly Report].[Date].[Date]").VisibleItemsList = Array( _
        "[Bi-Hourly Report].[Date].&[2016-09-28T00:00:00]")
End Sub

But it fails when i try to re-run the same macro that i just recorded (after changing the date back). I've enabled and disabled multiple selection option, etc... Every tip i've found... nada...

Not to mention, trying to auto-filter a chart is a nightmare because tables, yea there's tons of articles on it, but charts? not much comes up on researching...

Here's images of the filter button, because almost everything i've researched is to sort the COLUMN of the Table, not the filter itself with a PivotTable.

Table Filter

Chart Filter

Any help on this matter would be greatly appreciated! I cannot post the actual excel spreadsheets as they are proprietary property of the company, but i can replicate the format with false data if needed... I've been at this for almost a month now...

Thanks in advance!

can you hel me on this? stackoverflow.com/q/48442027/9267058 — MissTeppy

Aditya Pansare Aditya Pansare · Accepted Answer · 2016-10-01T06:58:28

check this out.

Dim prev_date As String

prev_date = Month(Date - 1) & "/" & Day(Date - 1) & "/" & Year(Date - 1)
Thisworkbook.Sheets("Sheet1").Activate 
'change this line with your sheet where pivot table is present. Change Sheet name.
ActiveSheet.PivotTables("PivotTable1").RefreshTable
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = prev_date

Excel VBA to filter Pivot Table and Pivot Chart for previous day - Pivot Filter Field

1 Answers