Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

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

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

Avatar of Dave
Dave
Flag of Australia image

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

ASKER CERTIFIED SOLUTION
Avatar of hitsdoshi1
hitsdoshi1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

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!