Solved

Auto update Pivot Table when Cell Changes

Posted on 2011-02-13
5
649 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:wlwebb
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
While you can do this in ThisWorkbook, its more effective and easier to debug by putting it in the sheet's codepage:

Private Sub Worksheet_Change(ByVal Target As Range)
    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 Sub

See attached corrected and functional.

Dave
PR-Summary-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
0
 

Author Comment

by:wlwebb
Comment Utility
Dave thanks I'll give it a try later this eve when back at office
0
 

Author Closing Comment

by:wlwebb
Comment Utility
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now