SpecialCells(Formula) --> Value

[Darren Hill]

I need to copy a worksheet, and convert all formulas in the copy to values.
I tried the following two methods without success. How can I do this?

Thanks in advance.

Sub CopyWSValues(ws As Worksheet)

ws.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
Cells.SpecialCells(xlCellTypeFormulas).Value =
Cells.SpecialCells(xlCellTypeFormulas).Value

Exit Sub ' other method below
ws.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Range("Print_Area").SpecialCells(xlCellTypeFormulas) =
_ ActiveSheet.Range("Print_Area").SpecialCells(xlCellTypeFormulas).Value

End Sub

Darren

 

[Jon Peltier]

SpecialCells(xlCellTypeFormulas) is likely to give you a multiple area
range. You could do a loop:

Dim rArea As Range
For Each rArea In .SpecialCells(xlCellTypeFormulas).Areas
rArea.Value = rArea.Value
Next

or simply

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

since it doesn't change anything for cells that don't contain formulas.

- Jon

 

[Dave Peterson]

If you're converting the formulas to values, you could drop the .specialcells()
stuff and just use the entire range.

with activesheet.usedrange 'did you really want to specify just the print_area?
.value = .value
end with

or
with activesheet.usedrange
.copy
.pastespecial paste:=xlpastevalues
end with

=====
If you wanted to use the .specialcells, then you'll have to do each area
separately:

dim myArea as range
for each myArea in activesheet.cells.specialcells(xlcelltypeformulas).areas
with myarea
.value = .value
end with
'or
with myarea
.copy
.pastespecial paste:=xlpastevalues
end with
next myarea

 

[Darren Hill]

Thanks, Jon and Dave. Lightning fast service!

Dave, you asked: "did you really want to specify just the print_area?"
No, but I was trying to limit the area being operated on.
I tried activesheet.cells.value = activesheet.cells.value and got "Out
of Memory".
I had forgotten about UsedRange! Thanks for reminding me.

Darren