I am trying to write a macro that updates my pivot table when new data is put into the original table. I keep on receive an error and do not know how to fix it. Here's what I have so far.
Sub UpdatePivotTableRange()
Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long
Set Data_Sheet = ThisWorkbook.Worksheets("Data insert")
Set Pivot_Sheet = ThisWorkbook.Worksheets("CC_Users")
PivotName = PivotTable6 Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
Pivot_Sheet.PivotTables (PivotName)
ChangePivotCache.ActiveWorkbook PivotCaches.Create SourceType:=xlDatabase, SourceData:=NewRange Pivot_Sheet.PivotTables(PivotName).RefreshTable
Pivot_Sheet.Activate MsgBox "Your Pivot table is now updated" End Sub
Thank you
This needs to be pasted in VBE on the sheet that holds your table that will be changed.
As is, this will refresh every pivot table (along with pivot charts) that exist on your workbook when ANY cell on your sheet with the table is changed.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.RefreshAll
Application.DisplayAlerts = True
Application.EnableEvents = True
Msgbox "All Pivots Refreshed"
End Sub