I have a strange one. I am looking to copy only the values from a pivot table into specific cells in another workbook, by changing the filter everytime. Here is my code so far, the copy part is out of my league and I am hopping that someone has an idea about this. Also, if the pivot for a filter choice is blank, can it be skipped?


Option Explicit

Sub FilterPivotTable()

Dim rLastCell As Range
Dim PvtTbl As PivotTable
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb2 As Workbook

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set wb2 = Workbooks.Open("filepath")
Set ws2 = wb2.Sheets("EX")

Dim rowCount As Long
Dim LastRow1 As Long
Dim pvtField As PivotField

Set PvtTbl = ws2.PivotTables("PivotTable2")

Application.ScreenUpdating = False

Set pvtField = PvtTbl.PivotFields("Divisions") 'extend etc as required

Dim myArr()
myArr = Array("Rail1", "Rail2", "RailC", "RailM", "Airborn", "Ret")

'PvtTbl.ManualUpdate = False

Dim i As Long

For i = LBound(myArr) To UBound(myArr)

pvtField.ClearAllFilters
pvtField.PivotFilters. _
Add Type:=xlCaptionContains, Value1:=myArr(i)

With ws1
'how can I copy the data in specific cell for each pivot filter change? In my case: D7, D23, D55, D66, D81, D106
End With

Next i

Application.ScreenUpdating = True
'PvtTbl.ManualUpdate = False
End Sub

Cross-post: https://www.excelforum.com/excel-pro...d-copy-it.html