Run macro only once, when file is opened

Gimics

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

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Gimics

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

Watch MrExcel Video

Forum statistics

Threads
1,108,631
Messages
5,523,992
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top