Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

Auto update Pivot Table when Cell Changes

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
    
    Call UpdatePaydatesPivot
    

    End If
    
End If
End Sub

Open in new window

PR-Summary.xlsm
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
However, if you want to keep it in ThisWorkbook, for some reason, then the code should have looked like this:  Note the active sheet in sheet change is
sh, so sh.name = "Payroll Data Input" then

is the only code change required.

Again, for proper maintenance, etc., code like this should go in the CODEPAGE of Payroll Input Data.  E.g., what if you have one of these type of activities on lots of sheets - then it would be lots of if statements or a select case statement to figure what sheet you were on, etc.  WHereas if you put it on the Sheet's CODEPAGE as suggested in my posting, above, its easier to maintain, etc.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.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
   
    Call UpdatePaydatesPivot
   

    End If
   
End If
End Sub
Note in my attached, I commented out the prevous sheet_change macro - not sure why you wanted to referesh all pivots on sheet change?

Dave
Avatar of wlwebb

ASKER

Dave thanks I'll give it a try later this eve when back at office
Avatar of wlwebb

ASKER

Dave
I incorporated the change to the code page as you suggested.  As you would suspect it works perfect.  Thanks.  Another question I had asked earlier produced that coding change.  I probably wasn't clear on an earlier question and that expert wasn't told that there might be more Pivot tables than one in my project.