Solved

Auto update Pivot Table when Cell Changes

Posted on 2011-02-13
5
689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34884435
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 42

Expert Comment

by:dlmille
ID: 34884459
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 42

Expert Comment

by:dlmille
ID: 34884466
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
ID: 34884546
Dave thanks I'll give it a try later this eve when back at office
0
 

Author Closing Comment

by:wlwebb
ID: 34885267
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 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