LinkBack URL
About LinkBacks
Is it more efficient to add and Active X button/VBA to update/refresh a pivot table and corresponding chart than to create a dynamic chart that updates from a spreadsheet when new data is added?
Last edited by Drew Goldberg; 08-19-2010 at 07:06 PM.
The button doesn't have to be an Active-X control. You can use a button from the Forms controls or a shape - just assign the macro code to it.
Your question of "efficiency" seems to have more to do with approach rather than calculation speed.
My take on this is . . . I use both approaches, depending on the project.
A dynamic chart is "dynamic" only in the sense that the data series are based on dynamic named ranges. The advantage of this approach is the chart updates in real time as data are added / deleted.
Pivot tables are very efficient but, as you point out, must be refreshed which is an extra step, but they add a degree of flexibility to manipulate the data by changing the row/page fields to instantly get different perspectives.
Something similar can be achieved by making dynamic charts interactive, allowing use of controls to add/delete/change series - but this involves a lot more work to achieve the same level of flexibility the PT offers.
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Palmetto:
Thanks for the feedback.
It appears that the built in functionality of the pivot table is the way to go.
Do you know what macro code would execute a pivot table refresh step?
I read that F9 would do it.
I assume the code would be to run F9 on the worksheet that has the pivot tavble, correct?
F9 recalculates the entire worksheet.
See this link on using VBA to Refresh Pivot Tables
Awesome!
I'll give it a try later tonight.
Thanks.
Palmetto:
When using the script provided by the link, the worksheet returns a runtime error of
"The macro 'Pivotmacro' cannot be found.
Does this mean a macro has to be created as well?
If so, would it be added to the the worksheet script?
Thanks,
Drew
You only need this code, copied into a standard module (taken from the link I gave).
You can assign the code to a button and run on demand when desired or, you could use a worksheet event to automatically refresh the PT, such as when the sheet is activated.
Please Login or Register to view this content.
This simple code calls the macro Refresh_Pivot_Table when the sheet, into which this code is placed, is activated.
This code would go into the worksheet module of the sheet that contains the Pivot Table.
Please Login or Register to view this content.
I must be overlooking a basic step or reference.
I have tried the code suggested and am running into another error:
"Compile error: Sub or function not defined"
Here are my steps:
1) Right click on the worksheet tab with the pivot table
2) Select "view code" option
3) Copy/Paste in the text supplied:
Option Explicit
Private Sub Worksheet_Activate()
Fresh_Pivot_Tables
End Sub
4) Close out VBA editor
5) Open adjacent worksheet tab
6) Reselect Worksheet tab with pviot table and script - >generating the error.
Any recommendations?
You haven't added the first bit of code Palmetto posted.
Remember what the dormouse said
Feed your head
I'm unsure where this code should be added.
Where should the first set of code be added vs the second set?
I tried to paste them both in the same worksheet.
Does one go in the workbook and the other in the worksheet?
The first bit goes in a normal module (Insert-Module in the VBEditor if there isn't one already), the second goes in the worksheet.
Re: Use of Active X or Dynamic Chart for updating
Awesome, works great.
Very new to VBE...
Thanks for the assistance.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks