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.