 | You 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 Excel Helper on | |