Code that will rerun or refresh a pivot table (after new data ispasted into the original Pivot Table's Source Range)
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! |
Code that will rerun or refresh a pivot table (after new data is p
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 he http://www.ozgrid.com/VBA/pivot-table-refresh.htm Regards, Ryan-- -- RyGuy "Mike C" wrote: 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! |
Code that will rerun or refresh a pivot table (after new data is pasted into the original Pivot Table's Source Range)
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 PTTable OptionsName Private Sub Worksheet_Activate() ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub -- Regards Roger Govier "Mike C" wrote in message ... 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! |
Code that will rerun or refresh a pivot table (after new data isp
On Feb 13, 12:16*pm, ryguy7272
wrote: 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 hehttp://www.ozgrid.com/VBA/pivot-table-refresh.htm Regards, Ryan-- -- RyGuy "Mike C" wrote: 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!- Hide quoted text - - Show quoted text - ....And you were right, the code you provided was just what I needed! Thanks again. |
All times are GMT +1. The time now is 10:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com