Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Auto update Pivot Table when Cell Changes

Posted on 2011-02-13
5
Medium Priority
?
696 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

722 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