Home > Forum Home > Charting and Performing Technical Analysis > Change the range of pivot table dynamically > Change Pivot Cache with VBA Share

Change Pivot Cache with VBA

Excel Help for Change Pivot Cache With Vba in Charting and Performing Technical Analysis


Forum TopicLogin

Change Pivot Cache With Vba

Rate this:
(3/5 from 17 votes)
HappyYou can change the pivot table source data dynamically within a VBA macro.
In Sheet1 you first need to get the last row number such as:

VBA Code:
Dim lastrow as Integer
Sheets("Sheet1").Select
Range("A1").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row

Then use the last row number to refresh the pivot table source data range such as:

VBA Code:
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R" & lastrow & "C3")

 Excel Business Forums Administrator
 Posted by on
 
View Full Post

Find relevant Excel templates and add-ins for Change Pivot Cache with VBA in the