copying all formulas, except sum
Thanks! It seems to work fine.
regards,
Hans
-----Original Message-----
Hans,
something like this should work:
Sub RemoveFormula()
Dim wk as worksheet
dim cl as range
for each wk in activeworkbook.worksheets
for each cl in wk.usedrange.cells
if left(cl.formula,5) < "=SUM(" then
cl.value = cl.value
end if
next cl
next wk
end sub
'Cheers, Pete.
-----Original Message-----
I was wondering if there is a relatively easy way to
convert all data in a spreadsheet to values, except the
SUM formulas.
We have a report that we need to send around every month
and it is made from a workbook that contains a lot of
lookup formulas and references to other workbooks,
sheets,
pivottables etc. What we did until now, is copying the
sheets we need and paste all the data to values. For
check
purposes, however, it would be better if we could keep
the
SUM formulas in the spreadsheets.
It concerns quite a lot of sheets and therefore I don't
want to make additional (simplified) spreadsheets that
would be easier to process in a macro.
Does anyone have any ideas how to tackle this?
Many thanks in advance for your help.
Hans
.
.
|