update worksheet keeping formula
 

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.

update worksheet keeping formula
 

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.

update worksheet keeping formula
 

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

Emma.

"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.

update worksheet keeping formula
 

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.

update worksheet keeping formula
 

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.

update worksheet keeping formula
 

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.

update worksheet keeping formula
 

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


"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.

update worksheet keeping formula
 

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.

update worksheet keeping formula
 

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

"Bernie Deitrick" wrote:

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.