Macro to auto refresh pivot table on a filter

[myquan14]

In excel 2007, I have created a pivot table that uses a weight calculation in the data set and a calculated field to show weighted values in the pivot table.

My data set contains multiple time frames, for example 2011-1H or 2011-1Q. I am only using halves and quarters - which will determine my data set weights.

Time frame is used as my 'Report Filter' in my pivot table. When time frame is filtered in my pivot table to either half or quarter my weight calculation within my data set updates accordingly using an 'if' statement.

Here is my issue: I would like to automatically refresh my pivot table after I have filtered my time frame field. (The reason I need to do this is because my weight calculation changes depending on the time frame shown and my totals do not add to 100 unless i refresh the table.) As of now, I am only able to do so by manually refreshing the pivot table. I have created a button that refreshes the entire workbook but this isn't quite the result i want. This workbook is going to be used by a number of people and want to limit the amount of steps needed to manage the book.

In summary: I would like to refresh a pivot table automatically after a field filter.

Any thoughts? Thanks in advance.

 

[jeffreybrown]

Hello & Welcome to the board,

This could work...change the Pivot Table name...

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Me.PivotTables("PivotTable1").PivotCache.Refresh
    Application.EnableEvents = True
End Sub

 

[myquan14]

Worked like a charm!! Thanks so much!

 

[jeffreybrown]

You're most welcome