My objective is to filter a pivot table using a range in another sheet.
My pivot is in Worksheets("Aging Report").
My reference data using which I want to filter is in Columns A2 and onward in Worksheets("Instructions").
The below code worked on a table which did not have pivot table.
I need to take value from A2 then filter on ("Client ID") and export in Excel and save and so on with values in A3, A4, A5.
Sub Pivotfilter()
Dim varItemsToReplace As Variant
Dim varItem As Variant
Dim wksSource As Worksheet
Dim wksDest As Worksheet
Dim rngSource As Range
Dim rngSource2 As Range
Dim rngCell As Range
Set wksSource = Worksheets("Instructions")
Set wksDest = Worksheets("Aging Report")
With wksSource
Set rngSource = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
For Each rngCell In rngSource
With wksDest
ActiveSheet.PivotTables("PivotTable40").PivotFields ("Client ID")
.PivotFields(rngCell.Value).Visible = True
Dim wb As Workbook
Set wb = Workbooks.Add
Windows("XXXXXXX").Activate
Sheets("Aging Report").Select
Sheets("Aging Report").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\XXX\Desktop\SOA\" & .Range("B3").Value & " - " & .Range("B4").Value & ".xlsx"
wb.Close
Windows("XXXXXXX.xlsm").Activate
End With
Next rngCell
End Sub