Sub FilterPivotTableBasedOnSelectedTeams()
Dim pt As PivotTable
Dim selectedItemsRange As Range
Dim myCell As Range
Dim fieldName As String
Dim lastRowSelected As Long
Dim pi As PivotItem
Dim firstItemSet As Boolean
Set pt = ThisWorkbook.Worksheets(“PivotTable2”).PivotTables(“PivotTable2”)
lastRowSelected = LastRow(tblTemp.Name, 1)
Set selectedItemsRange = tblTemp.Range(“A1:A” & lastRowSelected)
fieldName = “Team”
pt.PivotFields(fieldName).ClearAllFilters
Dim itemsTotal As Long
itemsTotal = pt.PivotFields(fieldName).PivotItems.Count
For Each pi In pt.PivotFields(fieldName).PivotItems
If Not IsInRange(pi.Name, selectedItemsRange) Then
itemsTotal = itemsTotal – 1
If itemsTotal = 0 Then
Err.Raise 222, Description:=“No value in the pivot!”
Exit Sub
End If
pi.Visible = False
End If
Next pi
End Sub
Function IsInRange(myValue As String, myRange As Range) As Boolean
Dim myCell As Range
IsInRange = False
For Each myCell In myRange.Cells
If myCell.value = myValue Then
IsInRange = True
Exit Function
End If
Next myCell
End Function
Public Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(wsName)
LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row
End Function
دیدگاهتان را بنویسید