Pivot Table & VBA Code

[kishorkhanal]

I am using following code to update the pivot tables in another worksheet, when I select a worksheet. I have about 7 Pivot Tables that use the same source data. It takes a little while to update all 7 pivot tables after I select the sheet. Is it possible to make the update much faster?

Privatse Sub Worksheet_Activate()
Worksheets("Pivots").PivotTables("PivotTable1").RefreshTable
Worksheets("Pivots").PivotTables("PivotTable3").RefreshTable
Worksheets("Pivots").PivotTables("PivotTable4").RefreshTable
Worksheets("Pivots").PivotTables("PivotTable5").RefreshTable
Worksheets("Pivots").PivotTables("PivotTable7").RefreshTable
Worksheets("Pivots").PivotTables("PivotTable8").RefreshTable
Worksheets("Pivots").PivotTables("PivotTable9").RefreshTable
End Sub

 

[kishorkhanal]

Anybody can help this morning?

 

[kishorkhanal]

Instead of updating individual pivot tables, is it possible to update all the pivot tables in that sheet? Will it make it any faster? Please help.

 

[george hart]

Hi

Try this. Copy the code in a macro, select the sheet with the pivot tables in and run it.

Dim pt As PivotTable
Worksheets("Pivots").Activate
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt

End Sub

 

[kishorkhanal]

Hi

Try this. Copy the code in a macro, select the sheet with the pivot tables in and run it.

Dim pt As PivotTable
Worksheets("Pivots").Activate
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt

End Sub

How can I make the pivots update automatically using this code when I select the Worksheet "Filter"?

 

[george hart]

I'm not sure what you mean? Are you currently filtering to select specific information and then updating your pivots?

If you are, you could do someting similar to the code below prior to updating the pivots...:

Selection.AutoFilter Field:=11, Criteria1:="BEDFORD"
Selection.AutoFilter Field:=7, Criteria1:="Attribution Agreed"

 

[Misca]

Drop this piece of code to the sheet module (= click on the sheet name on the VBA Project window) of the worksheet "Filter":

Private Sub Worksheet_Activate()

Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
Next pt

End Sub

 

[kishorkhanal]

I did what you said but still does not work.

 

[Misca]

What do you mean it does not work? I mean have you added rows to your data and it's not picking up the new rows? And you have pasted the code in the sheet module and not in the normal module, right?

If your Pivot Tables were done using fixed ranges updating the tables won't expand the data range. It only gets the values from the given range and makes sure it's up to date.

If you want your Pivot Tables to pick up all the new lines of data you should use dynamic ranges to start with.

But if this isn't the case and you've changed your existing data range values and your pivot tables are still not updating, then I don't know what's the problem without seeing the file. The code seems to be working fine.