Pivot Table changes Averages to Sums after import from TOAD

[jrzygrrl]

Hello,
I have a pivot table that I want to show averages. I set it up to show everything as averages & save and close the file. I then have an automation that imports new data from TOAD into the file. When I open the file back up, the averages have all been changed to sums AND the Grand Totals on the columns have disappeared. I don't know if it's an excel or a TOAD problem though. Has anyone else had these issues? I should add that if I export the data from Toad into that same spreadsheet (while it's open) I don't encounter these problems.

 

[Kuljack]

I'm not familiar with TOAD, but will the workbook you open this pivot table from always be the same workbook?

My initial thought is just to create a brief macro that adjusts the pivot table to your desired settings upon opening, this way every time you open it (regardless of what's being down when it's closed) it'll refresh to your needs when opened.

 

[Kuljack]

Might try this, either from an event change within the Sheet itself, or just as a button to refresh the table:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of c").Function = _
        xlAverage
    'Repeat the above code for each column of data

    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True: .RowGrand = True
    End With