Solved

Auto update Pivot Table when Cell Changes

Posted on 2011-02-13
5
682 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

761 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