Excel VBA过滤前一天的数据透视表和数据透视表 - 数据透视表过滤器字段

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...

我已经在google(和这里)的各个地方进行了搜索,并尝试了各种来源的各种代码组合,包括这个网站,Excelguru,excelexperts,ozgrid,dedicatedsdex,excelcampus,spreadsheetguru等......等等向前...

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.

这是我的问题,每一点代码,每一个改动,每种类型,都不起作用。我在我的工作站点使用Office 360​​(最新),所以它是excel 2016和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.

我们每小时将数据输入excel表(Log)。在一天结束时,在凌晨5点,我们保存并关闭该日志,打开另一个将数据导入power pivot的excel表,并将其显示在数据透视表上(格式化我们的老板打印),我们选择使用过滤器下拉列表显示上一个日期的过滤器,并将其打印出来。我们使用三(3)个报告执行此操作:2个数据透视表和1个数据透视图。 Power Pivot从日志表导入所有数据以重新格式化以进行打印。

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...

但是当我尝试重新运行我刚录制的相同宏(在更改日期之后)时它失败了。我启用并禁用了多个选择选项等...我发现每个提示... 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.

这里是过滤器按钮的图像,因为我研究过的几乎所有内容都是对表的COLUMN进行排序,而不是使用数据透视表对过滤器本身进行排序。

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...

任何有关此事的帮助将不胜感激!我不能发布实际的excel电子表格,因为它们是公司的专有财产,但如果需要的话,我可以使用虚假数据复制格式......我已经在这里待了差不多一个月了......

Thanks in advance!

提前致谢!

1 个解决方案

#1

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