Automatic updates on Save

timspin

Board Regular
Joined
Nov 18, 2002
Messages
231
Hi Experts

I have a spreadhseet that utilizes pivit tables, to straight Excell data to charts.

The sheet with charts is saved to webpage.

What I would like is a macro that when a user updates data in the spreadsheet and hits save:

1) All the pivot tables are updated
2) The Charts are updated
3) The Webpage is automaticaly published

I think the charts get updated automaticaly on save, I have also selected the option to update the published web page automaticaly on save. How do I get the pivot tables to update on save?

Thanks for any help.
Regards
Tim
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi Tim,

You could try the code below.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim PC As PivotCache
    For Each PC In ThisWorkbook.PivotCaches
        PC.Refresh
    Next PC
End Sub

Be sure to test that your sequence for these 3 steps is such that the Chart and PivotTables
are updated prior to changes being published to the web page.
 
Upvote 0
That works thanks, though now everytime I save my workbook creates a completely new set of charts (perfectly aligned on each other) - you wouldnt notice unless you move one, only to find an identical copy underneath.

If I hit save three time ihave three copies of the chart.

Also have to save as a macro enable workbook, not just click save, isthere anyway around that as well.

Thanks
Tim
 
Upvote 0
Regarding the first problem with the PivotCharts, you could try this and see if it works better. I don't work much with PivotCharts, but I'm surprised that refreshing the PivotCaches would create duplicates.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
End Sub

Regarding not using the macro-enabled workbook, I don't think there is a better alternative to automatically do what you describe.

One possible alternative is to setup you datasource as an data connection to an external source. This would allow you to refresh the data every X minutes that you set.
However that leaves a possible gap if your file is saved and published less than 1 minute after your data is changed.

What concern(s) do you have about using a macro-enabled workbook?
 
Upvote 0

Forum statistics

Threads
1,222,031
Messages
6,163,496
Members
451,839
Latest member
HonestZed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top