-2

I have a dashboard by pivot charts. The source data gets updated via a macros. However, when i refresh data for the pivot tables, the source data table gets reduced to the headers of the source file? Is there a way I can refresh these pivot tables without having to select the source data every time I update the source sheet by running the macros ?

Ankit Sharma
  • 1
  • Do you have access to update the macros? – Earl_of_Duke Jun 23 '19 at 11:12
  • The code would vary based on how the source file was set up (in Excel on a worksheet with nothing else, in a table on a sheet, on the same sheet as other data, etc.). It sounds like your existing code may be attempting to recreate the pivot table instead of refreshing. – Jenn Jun 23 '19 at 17:56

1 Answers1

1

If you just need to refresh your pivot table, you could add a new sub for only that reason. Here is an example:

Sub RefreshPivotTable()

Dim ws As Worksheet 'Sheet containing the pivot table
Dim rng As Range 'top left cell of the pivot table
Dim pt As PivotTable

Set ws = Worksheets("Pivot Cohort")
Set rng = ws.Range("G1")
Set pt = rng.PivotTable

'~~> Refresh only this pivot table
pt.RefreshTable

'~~> Or Refresh all tables and queries in the active workbook
ActiveWorkbook.RefreshAll

'~~> Clear the variables from memory
Set ws = Nothing
Set rng = Nothing
Set pt = Nothing

End Sub
Jenn
  • 612
  • 1
  • 4
  • 7