如何使用正确的数据透视表 VBA 子字段筛选器

huangapple go评论121阅读模式
英文:

How to use correct Pivot Table VBA sub-field filter

问题

一直在尝试解决这个问题,但仍然没有成功。我尝试创建一个 VBA 宏,它查看单元格 A1 中的值,并将其应用于 C4 中的筛选器,但始终出现“无法获得 PivotTable 类的 PivotFields 属性”的错误。

这表明代码找到了字段名称 "Department"。筛选器基于子字段名称 "Department" 中的数据(字段名称也是 "Department")。

是否有人能够指导如何在代码中正确输入这个?

Sub FilterByDepartment()
    Dim WS3 As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim ayearweek() As Variant

    ' 设置工作表
    Set WS3 = ThisWorkbook.Sheets("Sales Qty")

    ' 关闭计算和屏幕更新
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    ' 从单元格 A1 读取值到 ayearweek 数组
    ReDim ayearweek(1 To 1)
    ayearweek(1) = WS3.Range("A1").Value

    ' 刷新主数据模型以进行文章检查和查询
    Set pt = WS3.PivotTables("Master Model")
    pt.PivotCache.Refresh

    ' 设置数据透视表接受多个值
    pt.CubeFields("[Department].[Department]").EnableMultiplePageItems = True

    ' 应用部门筛选器
    Set pf = pt.PivotFields("[Department].[Department]")
    pf.ClearAllFilters
    pf.CurrentPage = ayearweek(1)

    ' 清理
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

请注意,上述代码中已经将 HTML 编码 """ 转换回双引号(")以使代码有效。

英文:

Been trying for days to solve this problem and still no joy. I have tried to make a vba that looks at the value in cell A1 and applies it to the filter in C4 but keep getting a "Unable to get the PivotFields property of the PivotTable class" error.

This would insinuate that the code is finding the field name "Department". The filter is based off the data in the sub-field name "Department" (the field name is also "Department"

Would anyone be able to advise how to input this correctly in to the code?

Sub FilterByDepartment()
Dim WS3 As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim ayearweek() As Variant

' Set Worksheet meaning
Set WS3 = ThisWorkbook.Sheets("Sales Qty")

' Turn off calculation & screen updating
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

' Read the value from cell A1 into the ayearweek array
ReDim ayearweek(1 To 1)
ayearweek(1) = WS3.Range("A1").Value

' Refresh Master Model for article check and query
Set pt = WS3.PivotTables("Master Model")
pt.PivotCache.Refresh

' Set the Pivot table to accept multiple values
pt.CubeFields("[Department].[Department]").EnableMultiplePageItems = True

' Apply the Department filter
Set pf = pt.PivotFields("[Department].[Department]")
pf.ClearAllFilters
pf.CurrentPage = ayearweek(1)

' Tidy Up
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

如何使用正确的数据透视表 VBA 子字段筛选器

答案1

得分: 1

I've translated the code part for you:

"找出了问题,部门需要进一步的[部门]条目,并且该值需要合并到过滤器条目中:

'声明工作表维度'
Dim Wb1 As Workbook
Dim WS1 As Worksheet
Dim WS3 As Worksheet
Dim WS5 As Worksheet

'设置工作表的含义'
Set Wb1 = ThisWorkbook
Set WS3 = ThisWorkbook.Sheets("Sales Qty")

'关闭计算和屏幕更新'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'刷新主模型以进行文章检查和查询'
WS3.PivotTables("Master Model").PivotCache.Refresh

'获取最后数据点的行号'
FILLY = WS3.Range("A1").Value

Range("C4").Select
ActiveSheet.PivotTables("Master Model").PivotFields("[Department].[Department].[Department]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Master Model").PivotFields("[Department].[Department].[Department]").VisibleItemsList = Array("[Department].[Department].&[" & FILLY & "]")

'整理'
Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic"
英文:

Worked it out, the department needed a further [Department] entry and the value needed to be incorporated in to the filter entry:

'Declare Worksheet Dims'
Dim Wb1 As Workbook
Dim WS1 As Worksheet
Dim WS3 As Worksheet
Dim WS5 As Worksheet

'Set Worksheet meaning'
Set Wb1 = ThisWorkbook
Set WS3 = ThisWorkbook.Sheets("Sales Qty")

'Turn off calculation & screen updating
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'Refresh Master Model for article check and query
WS3.PivotTables("Master Model").PivotCache.Refresh

'Get row number of last data point
FILLY = WS3.Range("A1").Value

Range("C4").Select
ActiveSheet.PivotTables("Master Model").PivotFields("[Department].[Department].[Department]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("Master Model").PivotFields("[Department].[Department].[Department]").VisibleItemsList = Array("[Department].[Department].&[" & FILLY & "]")

'Tidy Up
Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

huangapple
  • 本文由 发表于 2023年6月6日 17:20:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76413162.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定