Problem when refreshing pivot tables

[AdrienDevoe]

Hello I need your help, another thread had this question years ago, but no answer…
If I add data to empty rows in the pivot table’s source field, then right click the pivot table and select “Refresh”, the pivot table does NOT refresh.

In order to refresh the pivot table, I have to right click, select “Edit Layout” to launch the Pivot Table Layout window, then select “OK”.

How can I get the “Refresh” option to actually refresh? Or is Refresh only for changes in existing data, and not for adding data to empty cells that are already in the pivot table’s source field of cells?

I tested macros but didn’t worked out…

LibreOffice 7.3.7.2
Linux

[EarnestAl]

It depends on how you have defined your source data. If you have defined your source $A$1:$B$75 and you add data from A76:B100 then when you click Refresh, nothing will happen.

There isn’t any great speed loss by having the range from A1:B10000 set from the beginning even though there isn’t data in most of it. When you add source data then pressing Refresh will update the pivot table with the new data

[AdrienDevoe]

I’ve found a solution.
Before refreshing I go to Data>Calculate>Recalculate Hard

Then I use this macro

Sub RefreshAllPivotTables
    Dim oSheets as Object
    Dim oSheet as Object
    Dim oPivotTables as Object
    Dim oPivot as Object
    
    oSheets = ThisComponent.Sheets
   
    For Each oSheet in oSheets
        oPivotTables = oSheet.DataPilotTables
        If oPivotTables.getCount() > 0 Then
            For Each oPivot in oPivotTables
                oPivot.refresh()
            Next oPivot
        EndIf
    Next oSheet
End Sub

and it worked!

[erAck]

You could the macro also let do the recalculation:

ThisComponent.calculateAll()