Automatically refresh pivot tables after data entry

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.