I've just had the same issue and I thought the info on this page would help
me resolve it, my hopes were dashed when I read your reply. Then the cavalry
arrived. The resolution I've used is as follows:

' Set the source range of cells to be used to refresh the Revenue pivot table
Dim revRange As Range
Windows("Revenue 2008.xls").Activate
Sheets("Revenue").Select
Set revRange = Range("C1", Range("C1").SpecialCells(xlCellTypeLastCell))

' Now refresh the pivot tables
Windows("Pivot 2008.xls").Activate
Sheets("Rev").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivot Cache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=revRange, _
Version:=xlPivotTableVersion10)

Hope it helps