I'm stuck and would appreciate some help with the following:

I have a workbook with a Financial Model in Sheet 1 which contains Data for all the stocks in an index grouped by sector.

Each sector is headed by a row containing, in ("A(x))", a toggle button to expand/collapse that sector and show the constituent stocks. The sector row-positions will change from time to time as the index is recalculated.

All the information is determined when the data is loaded (on a weekly basis) into sheets 2 - 20 (one for each sector). A procedure calculates the dimensions of the sectors and performs a number of calculations before dumping the information into the model in sheet 1 under the relevant sector rows.

At this stage, the toggle-button subs have the size-variables stored as the sub that determined them has just been run. However, when the worksheet is closed and subsequently reopened, the variables are no longer defined. My crude solution at the moment is to declare these variables by hand as constants in the worksheet ("MAIN").

However, to fully automate the process, how can I ensure that the the variables calculated during the initial data input are stored and available in Sheet1 at all times. All the calculations in the Model are based on these variables and its crucial that they are automated to avoid any human errors.

Apologies if it's unclear. I have included a sample to illustrate the problem.

One solution was to load the variables calculated initially into an area of redundant cells in the worksheet, which can be accessed by the toggle-button subs at any time. I would however prefer to use Public variables, constants or something similar.

Help is much appreciated.

JP