I have modified a Macro Enabled workbook that when the user selects a year from a drop down I want it to update a Pivot Table that includes all paydates from that year.
What I have isn't working. This is an Excel 2007 file.
The user selects the year from a drop down validation list on worksheet "Payroll Data Input" at cell W1.
The pivot table is on worksheet "Pay Dates" The Report Filter for the selected year (which should change to the Year selected from cell W1 of "Payroll Data Input" is at cell B2 and the Pivot Table data heading is at a5 with data results of the Pivot table starting at a6
Below is the code as well as the file
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Payroll Data Input" Then
If Target.Address = "$W$1" Then
myvalue = Sheets("Payroll Data Input").Range("W1")
Sheets("Pay Dates").PivotTables("PivotTable2").PivotFields("Pay Date Calender Year").ClearAllFilters
Sheets("Pay Dates").PivotTables("PivotTable2").PivotFields("Pay Date Calender Year").CurrentPage = myvalue