Removing formulas and keeping results

  • Thread starter Thread starter dutty2001
  • Start date Start date
D

dutty2001

I am currently using MATCH formulas to pull certain data from SHEET1 and
place the appropriate data in various cells in SHEET2.

My problem is that now I want to keep SHEET2 as it is but also to Save
As SHEET2 to a different location and for it to stop updating the
information in it when I make changes to SHEET1. I basically want
SHEET2 to be an independant spreadsheet after I pull the appropriate
information from SHEET1.

So is there a way to remove the many formulas that are in the cells of
SHEET2 and just keep the text of the results in those cells? Is this
possible?
 
Select the entire sheet, copy, then paste values onto new
sheet (or in excel 2000 and earlier it is paste special
then values).
 
-----Original Message-----
I am currently using MATCH formulas to pull certain data from SHEET1 and
place the appropriate data in various cells in SHEET2.

My problem is that now I want to keep SHEET2 as it is but also to Save
As SHEET2 to a different location and for it to stop updating the
information in it when I make changes to SHEET1. I basically want
SHEET2 to be an independant spreadsheet after I pull the appropriate
information from SHEET1.
Click to expand...

Highlight the whole sheet and
right click the mouse and select the copy
right lick again and select "Paste Special"
then select "Value"

This will replace all formulas with the result value.

George
 
You copy it to itself and then past only the values.

Start with <Ctrl> A
Right click in the selection and choose "Copy",
Right click again, and choose "PasteSpecial",
Click on "Values", and then <OK>.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I am currently using MATCH formulas to pull certain data from SHEET1 and
place the appropriate data in various cells in SHEET2.

My problem is that now I want to keep SHEET2 as it is but also to Save
As SHEET2 to a different location and for it to stop updating the
information in it when I make changes to SHEET1. I basically want
SHEET2 to be an independant spreadsheet after I pull the appropriate
information from SHEET1.

So is there a way to remove the many formulas that are in the cells of
SHEET2 and just keep the text of the results in those cells? Is this
possible?
 
Create a module and use the following vb code

when you want to remove formulas just highlight the cells
and run the macro it will remove the formulas for you and leave the
valuse

Randall

Sub MakeValues()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlFormulas))
Cell.Value = Cell.Value
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
A small change can make this a bit faster:

Public Sub MakeValues()
On Error Resume Next 'In case no cells in selection
With Intersect(Selection, Selection.SpecialCells(xlFormulas))
.Value = .Value
End With
On Error GoTo 0
End Sub
 
This could cause problems if that intersection was discontiguous (at least it
did for me in xl2002).

You could loop through the areas in that selection and do .value = .value

But if the OP were converting to values, it wouldn't hurt to do all the
cells--formulas and constants.

with selection
.value = .value
end with

Or just copy|paste special values

with selection
.copy
.pastespecial paste:=xlpastevalues
end with
application.cutcopymode = false

The only difference I've seen between these two approaches occurs when the cells
with constants have different formats within the cell. (Some characters
bold/red, some not kind of thing.)
 
Back
Top