Emma,

One possiblility is that the cells with the formulas were formatted for Text
AFTER having the formulas entered. If that is the case, change the
formatting of those cells to General.

Bernie


"user3307" wrote in message
...
ok now for the selection of cells, still can't get 2nd macro to work
though... it leaves the text in and adds an extra space in front?
Thanks again!

"Bernie Deitrick" wrote:

Emma,

Now, that's much nicer than addressing you as "user3307"...

What you're doing wrong is just this: trusting me ;-)

I copied the wrong version of the first macro out of my library of code.
Try the version below.
The seocnd macro still works fine for me - try it after getting the first
one to work.

HTH,
Bernie
MS Excel MVP

Sub FormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub





"user3307" wrote in message
...
Bernie,
I can't make the 2nd macro work - it just adds a space in front of the
cell
text.
Also, the 1st macro changes all of sheet "a", not just the selected
cells.

What am I doing wrong?

Many thanks.

"Bernie Deitrick" wrote:

user3307,

Don't people use their names any more??


You can do this a few ways.

Better:

Select all the formulas on sheet "a" with cell references, and run the
first macro. Then reload
the
sheets "x, y and z", and then re-select all the formulas on sheet "a"
with cell references, and
run
the Second macro.


Less Good:
Or replace all the cell references in all your formulas with INDIRECT
function calls - very
tedious,
and prone to error, so not really recommended.


HTH,
Bernie
MS Excel MVP


Sub SAFormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub


Sub SATextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

"user3307" wrote in message
...
In a workbook I have a worksheet "a" which pulls information
(copies) from
worksheets "x, y, z".
I now want to update the workbook by sending only "x, y, z" to the
various
people updating the data.
How can I reload these worksheets to the workbook while keeping the
links
from "a" to "x, y, z"?

Thank you.