SpecialCells(Formula) -- Value
 

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(xlCel lTypeFormulas) =
_ ActiveSheet.Range("Print_Area").SpecialCells(xlCel lTypeFormulas).Value

End Sub

Darren

SpecialCells(Formula) -- Value
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Darren Hill" wrote in message
...
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(xlCel lTypeFormulas) = _
ActiveSheet.Range("Print_Area").SpecialCells(xlCel lTypeFormulas).Value

End Sub

Darren

SpecialCells(Formula) -- Value
 

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 wrote:

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(xlCel lTypeFormulas) =
_ ActiveSheet.Range("Print_Area").SpecialCells(xlCel lTypeFormulas).Value

End Sub

Darren

--

Dave Peterson

SpecialCells(Formula) -- Value
 

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

Dave Peterson wrote:
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 wrote:
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(xlCel lTypeFormulas) =
_ ActiveSheet.Range("Print_Area").SpecialCells(xlCel lTypeFormulas).Value

End Sub

Darren