Upadte PivotTable Data range

  • Thread starter Thread starter Taffy
  • Start date Start date
T

Taffy

Hi all,
I am trying to create Macro in excel 2010 which will change the data range for a pivot table if have added extra datainto the source data worksheet. This is the macro belowbut it returns a runtime error 5 "Invalid proceduer Call) error:

Sub ChangePivotTableDataRange()

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("Sales").Range("A5").CurrentRegion.Address _
, Version:=xlPivotTableVersion14)

End Sub

Any help on this would be greatly appreciated.

Taffy
 
Try :
ActiveSheet.PivotTables(1).SourceData = "Sales!" &
Sheets("Sales").[A5].CurrentRegion.Address
Daniel
 
Hi all,

I am trying to create Macro in excel 2010 which will change the data range for a pivot table if have added extra datainto the source data worksheet. This is the macro belowbut it returns a runtime error 5 "Invalid proceduer Call) error:



Sub ChangePivotTableDataRange()



ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _

PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("Sales").Range("A5").CurrentRegion.Address _

, Version:=xlPivotTableVersion14)



End Sub



Any help on this would be greatly appreciated.



Taffy

Many thanks for the help - I will give it a go an dlet you know if it works.

Taffy
 
Back
Top