Run macro only once, when file is opened

Gimics

Board Regular
Joined
Jan 29, 2014
Messages
164
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a workbook with a macro that saves a bunch of additional workbooks when it runs. I would like to immediately refresh the pivot tables of those workbooks the first time they are opened, but then only have them refreshed manually from then on.

I think I need to add a Refresh.all macro in the Workbook_Open() event and use an if statement with some form of public variable, that crosses between macros? I think the macro that saves the report would have to turn the variable on (say, set it to 1), and the Workbook_Open() macro would have to check if the variable was 1 and refresh, or do nothing. I'm not sure where/how to declare the variable so that I can use it across both macros?

Thanks
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
I resolved this issue by creating a named constant in my workbook, and setting it to 1 before I run my report macro.

In my Workbook_Open(), I checked if the named constant was = "=1" (Couldn't figure out why, but when you set the value of a named constant to equal 1, the stored value is "=1"), ran the refresh macro if it was, and then set the constant back to 0.
 
Upvote 0

Forum statistics

Threads
1,218,576
Messages
6,143,315
Members
450,477
Latest member
teresab543

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