VBA code to auto refresh Pivot table

You need to record a macro refreshing the pivot then call it from the Worksheet_Change event of the sheet the data is in.

Hi try ..

Sub MyMacro()
'My code....
ActiveWorkbook.RefreshAll
'Or
'ThisWorkbook.RefreshAll
End Sub

Read...
http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Not really, as i dont know the names of the pivot,.

in Trans sheet

you will trigger the code from the Worksheet_Change() event, use the drop downs at the top of the code window.

Do you mean you need to expand the no of rows the pivot table is using as it's source?

If you do i would suggest you convert the data on 'Trans sheet' to table format and then redoing the pivot table.

Then you should be able to use something like this.

Sheets("Masterfile").PivotTables(1).PivotCache.Refresh ' change Masterfile if that's not the actual name with the pivto table on it.

Inside sheet module

Private Sub Worksheet_Activate()
'YourCode here
End Sub

Hi,

The first thing to do is apply a dynamic range name to the source sheet data. Typically this name would be defined something like

=Offset(SourceSheet!$A$1,0,0,COUNTA(SourceSheet!$A:$A),COUNTA(SourceSheet!$1:$1)

Then just use the range name in the PT Source Source Data.

I tend to use the Activate event on the sheet that contains the PT to refresh the PT, i.e.

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Richard

Wouldn't converting the data in to a table/listobject do much the same thing as setting up a dynamic named range?

@Norie,

Yes indeed. Thanks for picking that up.

Although I must admit I tend to avoid the 'new' Table functionality, I just don't like the way it complicates formulae when you need to refer to columns in the Table. It just doesn't seem as user friendly somehow which is why I tend to forget about it when I would agree that it does offer another way forward.