Automatically refresh pivot tables after data entry

**Hodgepodge** · 03-20-2009, 11:09 AM

Hello,
This is the first time I’ve tried to use VBA. I’m using Excel 2000. In my excel workbook have 1 sheet called “Cards” in which I Change data in one cell $B$2 (enter a part number) and formulas in this sheet return many values from another sheet “Card Data”. In the “Card Data” sheet, formulas that look through a sheet “Sales Book” and return the data that pertains to the Part Number I entered in the sheet “Cards”. In the sheet “Card Data” there are 3 pivot tables (“PH CALC”, “PH QTY”, and “Pivot Table 3”) that use the Part Number data to show 3 different sets of information. The pivot tables are the source for 3 charts in the “Cards” Sheet.

Problem: Pivot tables don’t automatically refresh. I would like to use VBA code to automatically refresh the pivot tables in the “Card Data” sheet when I change the Part Number in cell $B$2 of the “Cards” sheet, which, in turn, would then update my charts in the “Cards” sheet.

I have Googled this for several days and found some ideas but just couldn’t them to work. I’m thinking I might be close. Here is some code I’ve tried:

(I tried to record a Macro just trying to get one pivot table to refresh. )

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 3/19/2009 by Blue Streak Grinding
'

'
    Range("B3").Select
    Sheets("Card Data").Select
    Range("N16").Select
    ActiveSheet.PivotTables("PH CALC").RefreshTable
    Sheets("Mike's Cards").Select
End Sub

(That never worked. I found this online and tried to get a pivot table to refresh on sheet activate and it didn’t work)

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
          
    Sheets("Card Data").PivotTables("PH CALC").RefreshTable
     
End Sub

Do I need to change some sort of formatting? I’m pretty sure it's just that I don’t know what I’m doing. Would it help if I was using Excel 2007? I could buy that if necessary.

I would like the tables to refresh when cell $B$2 of “Cards” in manually changed. Any help would be greatly appreciated.

**Spellbound** · 03-21-2009, 07:09 AM

Hello Hodgepodge

Not sure if this will help because I am not very knowledgeable in VBA and these bits of code are working with Excel 2003 but I have no idea if they will work with Excel 2000.

I use the following macro to refrsh all my Pivot Tables

Sub RefreshAllPivots()
    
    Dim wks As Worksheet
    Dim pt As PivotTable
    
    For Each wks In Worksheets
        For Each pt In wks.PivotTables
            pt.RefreshTable
        Next pt
    Next wks
    
End Sub

and I use the following code to trigger a worksheet to calculate itself:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$2" Then Exit Sub
    ActiveSheet.Calculate
End Sub

which could be amended to Call the RefreshAllPivots macro instead.

As I said, no idea if they will work with your version of Excel but they might just help.

spellbound

**Hodgepodge** · 03-21-2009, 11:43 AM

Spellbound, you Rock!!

Here is the combination that worked.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet
Dim pt As PivotTable
If Target.Address <> "$B$2" Then Exit Sub
For Each wks In Worksheets
For Each pt In wks.PivotTables
pt.RefreshTable
Next pt
Next wks
End Sub

Thank you so much!

**Spellbound** · 03-21-2009, 11:48 AM

Glad I could help.

As I said, I have no real knowledge of VBA but have made use of these pieces of code within my own projects.

spellbound

PS: Don't forget to mark the thread as SOLVED and if are inclined, check the scales to add to my reputation ....thanks

**sunnykumar08** · 02-14-2011, 06:27 AM

Thanks a lot !!! It helped me !

Originally Posted by Spellbound

Hello Hodgepodge

Not sure if this will help because I am not very knowledgeable in VBA and these bits of code are working with Excel 2003 but I have no idea if they will work with Excel 2000.

I use the following macro to refrsh all my Pivot Tables

Sub RefreshAllPivots()
    
    Dim wks As Worksheet
    Dim pt As PivotTable
    
    For Each wks In Worksheets
        For Each pt In wks.PivotTables
            pt.RefreshTable
        Next pt
    Next wks
    
End Sub

and I use the following code to trigger a worksheet to calculate itself:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$2" Then Exit Sub
    ActiveSheet.Calculate
End Sub

which could be amended to Call the RefreshAllPivots macro instead.

As I said, no idea if they will work with your version of Excel but they might just help.

spellbound