copying all formulas, except sum

[Hans]

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

 

[Pete McCosh]

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.

 

[Hans]

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.

.