update worksheet keeping formula

[Guest]

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.

 

[Bernie Deitrick]

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(xlCellTypeFormulas)
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

 

[Guest]

thanks Bernie, very kind.
I will give this a try.

Emma.

 

[Guest]

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]

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

 

[Guest]

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]

Emman,

I have never had that problem (or, indeed, any problem) with the second
macro....What kind of strings do you get when you run the first macro?

Bernie

 

[Bernie Deitrick]

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

 

[Guest]

the cells were formatted to accounting - they are now general and it works!
thank you Bernie!