Solved

Excel 2007 VB to Automatically Refresh a Pivot Table on Sheet 2 when a specific cell from Sheet 1 Changes

Posted on 2011-02-10
3
670 Views
Last Modified: 2012-05-11
Hello all.

I've been working on an Excel 2007 WB and have had a lot of great help from many.  What I am now attempting to do is to Auto refresh a Pivot Table when data in a specific cell is changed.  The input is on one sheet and the Pivot table is on a different sheet all in the same workbook.

I found an earlier (2009) thread and attempted it but it is not working.  Here is what my background  is

Clerk inputs info in worksheet "Payroll Data Input".  In cell "W1" of that worksheet a validation list shows the available years.  That list is based upon a Pivot table on worksheet "Pay Dates" it is PivotTable2 which starts in cell o4.

What I want it to do is to refresh the data in the Pivot Table when the clerk selects what year they are working on.

Below is the code from an earlier thread here.  Anyone see why it isn't working.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim pc As PivotCache
   For Each pc In ActiveWorkbook.PivotCaches
      pc.Refresh
   Next pc
End Sub

Open in new window

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 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34867127
You can refresh all pivotables as below without looping

Do you have a sample file, it appears that your code could be more tailored to a specific cell change and specific pivotttable

Cheers

Dave
ActiveWorkbook.RefreshAll

Open in new window

0
 
LVL 9

Accepted Solution

by:
hitsdoshi1 earned 500 total points
ID: 34867132
Your code will trigger refresh whenever any cell is change, I think what you need to is the code I attached. You just want the pivot refresh when someone changes the year. This should do the job.

Good Luck!
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("PT2").PivotFields("Pay Date Calender Year").ClearAllFilters
        Sheets("Pay Dates").PivotTables("PT2").PivotFields("Pay Date Calender Year").CurrentPage = myvalue
    End If
End If
End Sub

Open in new window

0
 

Author Closing Comment

by:wlwebb
ID: 34869083
Perfection.  Wow you guys were quick!  I posted this and went to dinner.  Didn't think I'd have an answer that fast.  Thanks so very much!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 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