Dim rng as Range
Dim sh as Worksheet
for each sh in ThisWorkbook.Worksheets
set rng = sh.Range("A1").CurrentRegion
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.Formula = rng.Value
Next

Converts the formulas to values except in the first row.

--
Regards,
Tom Ogilvy

Andy Healey wrote in message
...
Dear all

I'm using excel 97 + a variety of operating systems,

I have a rather large workbook which is full of 64 sheets of formulas, a
lot
of the formulas are redundant one month but might be need the following
month. as such the file is over 15 MB in size.

What I have done in order to limit the size, is to put the formulas in row
1
and then if that sheet is need to paste the formula to the relevant cells
(2600 on each sheet.) but a lot of them are still not needed.

What I would like to do is then copy the cells and paste the value into
them, but whenever I try this excel crashes.
(Strangely enough if I try it on excel xp at home it works fine.)

The only work around I can think of is to have a macro to go through the
pasted range and if the value of the formula is zero to change it to zero
rather than have a long formula in there.

any ideas how to go about it, as I'm getting all confused between the
various nows, then and ifs.

many thanks

Andy