Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table

M

Mike C

Hello,

I have created the following named range:

=OFFSET('FL1'!$A$12,0,0,COUNTA('FL1'!$A:$A),COUNTA('FL1'!$12:$12))

But I cannot figure out how to update a pivot table's results
automatically (after pasting a new set of data into the pivot table's
original source field.

In essence, I have a report that I need to pivot every morning
(sometimes 5-6 times per day). I am hoping to simply paste the new
data into the original tab where my prior day's report data existed,
and then click on a button that will update the pivot table results
for the new data.

Does anyone have a some code that would allow this?

I have not figured out how to do it........

THanks for any suggestions!
 
R

Roger Govier

Hi Mike

You could paste the following code onto the sheet containing the Pivot
Table.
As soon as you go to the PT after pasting your new data, the PT will refresh
as the sheet is activated.
You will need to change the name of the PT to match your own.
Right click on PT>Table Options>Name

Private Sub Worksheet_Activate()

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

End Sub
 
M

Mike C

This should handle all your needs:
Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
                    pt.RefreshTable
        Next pt
    Next ws
End Sub

This, and other good things here:http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Regards,
Ryan--

--
RyGuy










- Show quoted text -

....And you were right, the code you provided was just what I needed!
Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top