Run macro only once, when file is opened

Gimics

Board Regular
Joined
Jan 29, 2014
Messages
134
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Gimics

Board Regular
Joined
Jan 29, 2014
Messages
134
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,646
Members
430,150
Latest member
amitk1

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
Top