Macro for popup window " update values"

[Chris D]

hello, I'll explain my problem:
In a workbook for daily reports each day a new worksheet is made. For day 1
the sheet is named " 1", for day 2 "2" etc.
Now I add a worksheet which I call "calculation". In this worksheet I have a
series of columns which have formulas that calculate data. Standard there are
formulas for 50 days( formulas like " sumif('1'!F15=......)
Now the problem starts when there are 10 worksheets( 10 days) and the
calculation sheet is prepared to calculate for 50 days.
When I run the calculation and excel gets to the formulas for day 11 a popup
screen " update values:11" appears, then I have to press "cancel" in this
popupscreen as many times that takes to get through until day 50.
I would like to have a macro code which automatically selects " cancel" when
the popupwindow appears.
Can anyone help me?

thanks

 

[Gary''s Student]

Change you approach.

Rather than add a worksheet every day, begin with 50 empty data sheets and
populate the appropriate one on a daily basis. You may have problems with
functions like AVERAGE(), but you can craft formulas to ignore missing data
if the missing data invalidates your results.

 

[Chris D]

Thanks for your reply.
I have also made a workbook as you stated in your reply. Problem is that the
guys in the field won't change their method, so if I want some useful data
out of the reports I have to stick with the existing system.
Only thing I need to know is how can I have a macro( which is linked to a
button that starts the calculation) automatically select "cancel" if the
popup window " update values" appear.
Any thoughts are welcome.

thanks.

Chris

 

[Gary''s Student]

I can duplicate your behavior, but I don't know how to fix it. I can't trap
the pop-up.

Once the pop-up appears, macros are locked out on my system.

Sorry

 

[Chris D]

Gary,

OK thanks. Anyway I need to proceed with document that has 50 worksheets.
Suppose when I start with 1 sheet visible, 49 hided and I want a macro to
select a hidden sheet to be unhided. Do you have a macro for that?

regards,

Chris