Links will not update when sheet number exceeds ~12.

[Oskar473]

VBA and Excel gurus-

I've written macros to create several sheets that are linked to each other.
I intend for the final resulting workbook to contain about 60 sheets. This
workbook has one main page, and three different sheets that are repeated
multiple times. These three sheets contain many links to each other for the
purpose of updating one of them.

The problem is:
In the final product, values are not updated when changes are made. It
appears that the problem begins after four sets of the three sheets are
created. There is one main reference page, and two other pages that are used
as templates (which are hidden). When the total number of sheets is 11,
values are updated automatically. When this number increases to 14, formulas
no longer update. Automatic calculations are on, and typing F9 does nothing.
The only solution is to enter over the formula one one of the sheets to
force recalculations. It helps nothing to leave all sheets visible.

What is causing this problem? Can Excel not handle more than 12 sheets?

Joe

 

[Oskar473]

I found two sloppy workarounds, but would like to fix the root cause of this
problem.

One is to type Ctrl+Shift+Alt+F9 after making a change, forcing
recalculation of the entire workbook. It appears that the keystroke is only
required one time.

Another is to add the following to (all) formulas:

+ (0 * Now())

The "Now()" function returns the current date and time, and forces
recalcuation constantly. By multiplying by zero and adding, the net result
is no change to the value in the cell. I fear that this may slow down the
system while this massive workbook with thousands of forumlas is open,
however, so I won't be using it.

Any futher suggestions will be much appreciated.