Here is my problem, every bit of code, every alteration, every type, doesn't 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 I'm 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.
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.
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.
