Pivot tables not able to auto refresh when additional lines added

[Ann Ooi]

Hi, I have few pivots table in my VBA, and it's all connect to same data source "CompileData", the pivot table should extend the range of data when weekly I added new lines. But no matter what types of code I put in, it seems like not working at all. I do not know what is the issue, I had tried few codes below and as attached.
I had set the auto refresh in pivot table options, but it will shows run time error if I put this code in private sub.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll

End Sub

Sub RefreshAllPivots()
   Dim PC As PivotCache
   Workbooks("Hotayi RMA Dashboard.xlsm").Activate
  
   For Each PC In ActiveWorkbook.PivotCaches
      PC.Refresh
   Next PC
End Sub

Sub RefreshPT()

Application.ScreenUpdating = False

Dim WS As Worksheet
Dim pt As PivotTable
   
    Workbooks("Hotayi RMA Dashboard.xlsm").Activate
   
    For Each WS In Worksheets   'Goes through each WS
            For Each pt In WS.PivotTables  'Goes through each PT in WS
                pt.RefreshTable
            Next pt
    Next WS
Application.ScreenUpdating = True

End Sub

 

[Dave87]

Those VBA codes just refresh the pivot tables, they don't refresh the pivot table source data range.

I would guess your pivot tables are set to a static range e.g. A1:C12, if you try changing your source data 'Compile data' to a table or else change your pivot table source data to a dynamic range using offset and count/counta it should solve your problem

 

[Ann Ooi]

Those VBA codes just refresh the pivot tables, they don't refresh the pivot table source data range.

I would guess your pivot tables are set to a static range e.g. A1:C12, if you try changing your source data 'Compile data' to a table or else change your pivot table source data to a dynamic range using offset and count/counta it should solve your problem

Hi Dave, you are correct, it's static. Do you mean I need to have code to generate pivot table everytime I run VBA? Currently, I do not rerun pivot table, as there's too many tables to generate, it also have charts linked to every pivot table, everytime rerun then the chart preset will mess up again. Or what it's the code that you mention to change the table source data?

 

[Dave87]

your issue isnt in VBA refreshing the pivot table, its in the pivot table source data range

1. can you change your pivot source data to a Table, click in your source data range, press Control + t and check the range corresponds to your pivot table source range.
2. Click OK, in the top left under 'File' on the ribbon you should see Table1 (or TableX where X is the table number)
3. Go your pivot table, change the Source Data to Table1 (or TableX as required)

now when you run your macros they should update the pivot table automatically

if the above isnt clear check out How to use the Dynamic Pivot Table in Excel or else YouTube/Google search "dynamic pivot table" which should explain it clearer than I can here

 

[Ann Ooi]

Hi Dave,
I follow your steps and created the table, but get this error message, I think it's because I have few pivot tables linked to the same data, so it's not working? ( as attached the error message).
I search online and tried few coding, all not working for mine. it always have this run time error 1004, when come to refresh the range. e.g below code.

'Change Pivot Table's data source range address
pvt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceAddress)

Not sure anyone can help on this? Any other coding to refresh source data range, or how to correct this error runtime issue?

Sub refreshDataRange()

Dim sht As Worksheet
Dim pvt As PivotTable
Dim StartPoint As Range
Dim rng As Range
Dim SourceAddress As String

'Enter Worksheet Name that holds your Pivot data source
Set sht = ActiveWorkbook.Worksheets("DataCompile")

'Enter first cell in your Pivot data source
Set StartPoint = sht.Range("A1")

'Create SourceData address
Set rng = sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
SourceAddress = sht.Name & "!" & rng.Address(ReferenceStyle:=xlR1C1)
'Get to current Pivot Tables to update new source data
'Worksheets(Worksheets.Count).Select
'Loop through and update pivot tables with new data source range

For Each pvt In sht.PivotTables

'Change Pivot Table's data source range address
pvt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceAddress)

'Ensure Pivot Table is refreshed
pvt.RefreshTable

Next pvt

'Completion Message
MsgBox "All Pivot Table Data Source Ranges have been updated in this worksheet!", vbInformation

End Sub

 

[Ann Ooi]

your issue isnt in VBA refreshing the pivot table, its in the pivot table source data range

1. can you change your pivot source data to a Table, click in your source data range, press Control + t and check the range corresponds to your pivot table source range.
2. Click OK, in the top left under 'File' on the ribbon you should see Table1 (or TableX where X is the table number)
3. Go your pivot table, change the Source Data to Table1 (or TableX as required)

now when you run your macros they should update the pivot table automatically

if the above isnt clear check out How to use the Dynamic Pivot Table in Excel or else YouTube/Google search "dynamic pivot table" which should explain it clearer than I can here

Hi Dave, I manage to solve the change Data Source issue, after deleting all the slicer added. Somehow, there's some hiccup, it works fine now anyway. But, still look forward if anyone able to advise, on the VBA code below, anyway to correct it, due to the run time error issue.