Amending Excel Pivot Table Criteria Programatically

Question

I have an Excel spreadsheet template. It contains pivot tables having criteria that specify the content of the spreadsheet.

I need to amend that criteria programatically from a macro running in a separate spreadsheet so that the template will feature different content.

What coding would be appropriate to achieve this

Answer 1

The code below will update the report filter on a pivot table in a separate workbook.

In this example the report filter is called 'Location' and it's value is changed to 'West'

The code assumes that the separate workbook is already open.

Sub UpdatePivotFilter()

Workbooks("Book1.xls").Sheets("Sheet1").PivotTables("PivotTable1"). _
 PivotFields("Location").CurrentPage = "West"

End Sub