时间戳

首页 > 解决方案 > VBA 从存储在动态范围中的值过滤数据透视表中的值

vba - VBA 从存储在动态范围中的值过滤数据透视表中的值

问题描述

我试图从存储在“str”范围内的值中仅选择 PivotTable1 中的值，不包括过滤器中的任何空白，通过运行下面的代码我没有在过滤器中获取值。这段代码有什么问题，是否有任何替代解决方案可以满足我的要求。

Dim star As String
Dim startRange As String
Dim lastRange As String

startRange = ThisWorkbook.Sheets("Peer Code").Range("J2").Address

lastRange = ThisWorkbook.Sheets("Peer Code").Range("J" & LR0).Address

 star = startRange & ":" & lastRange

 ThisWorkbook.Sheets("Conso_Input").Activate
 Dim PI As PivotItem
  
 With 
 Worksheets("Conso_Input").PivotTables("PivotTable1").PivotFields("FundCode")
    .ClearAllFilters
    For Each PI In .PivotItems
        PI.Visible = WorksheetFunction.CountIf(Range(star), PI.Name) > 0
    Next PI
 End With

新代码

     ThisWorkbook.Sheets("Peer Code").Activate
    Dim LR0 As Integer
   LR0 = Range("J" & Rows.Count).End(xlUp).Row
Dim star As String
Dim startRange As String
Dim lastRange As String

startRange = ThisWorkbook.Sheets("Peer Code").Range("J2").Address

lastRange = ThisWorkbook.Sheets("Peer Code").Range("J" & LR0).Address

 star = startRange & ":" & lastRange

ThisWorkbook.Sheets("Conso_Input").Activate

    Dim PvtTbl As PivotTable
    Dim PvtFld As PivotField
    Dim PI As PivotItem
    Dim MatchFound As Boolean, i As Long
    
    ' set the Pivot Table
    Set PvtTbl = 
   ThisWorkbook.Sheets("Conso_Input").PivotTables("PivotTable1")
    
    ' set the Pivot Field
    Set PvtFld = PvtTbl.PivotFields("FundCode")
    
   ThisWorkbook.Sheets("Conso_Input").Activate
   
   PvtTbl.ClearAllFilters
   
   For Each PI In PvtFld.PivotItems
   'For Each PI In .PvtTbl.PivotItems
   
        PI.Visible = WorksheetFunction.CountIf(Range(star), PI.Name) > 0
   Next PI

标签： vbapivot-table

解决方案

尝试这样的事情：

Dim wsPC As Worksheet, wsCI As Worksheet
Dim PvtTbl As PivotTable, rngStar As Range
Dim PvtFld As PivotField, PI As PivotItem, m

Set wsCI = ThisWorkbook.Sheets("Conso_Input")
Set wsPC = ThisWorkbook.Sheets("Peer Code")

Set rngStar = wsPC.Range("J2:J" & wsPC.Cells(Rows.Count, "J").End(xlUp).Row)

Set PvtTbl = wsCI.PivotTables("PivotTable1")
PvtTbl.ClearAllFilters
Set PvtFld = PvtTbl.PivotFields("FundCode")
For Each PI In PvtFld.PivotItems
    m = Application.Match(PI.Name, rngStar, 0)
    PI.Visible = Not IsError(m)
Next PI

推荐阅读