how to copy&paste range with formulas which are without frozen cells ($) ?

O

ocb

hi,

I've created a monster - huge reporting file, it takes lot of inpu
data and play with it in many ways in many workseets to create m
monthly 16page Production control and logistics report(6 divisions
european curriences, fx rates, delivery performance, and lot more) a
its very big I can coordinate cell links anymore.

My problem is: i have range with formulas, and result of those formula
im useing in God knows how many other formulas etc. I want to chang
those cells slightly so everything will suck new results, but i want t
keep also old calculation somewhere(next to it) if:
-I copy and paste all not frozen cells (w/o $) will change
-drag&drop whole range, it will update all links and i dont want this

question :confused: is there any way to clone range ? :]

thanks
danie
 
B

Bernie Deitrick

Daniel,

I _think_ this will solve your problem.

Select the range you want to copy, run the first macro below, copy the
range, do the paste on the new range, then run the second macro while you
still have the paste area selected.

HTH,
Bernie
MS Excel MVP

Sub FormulaToText()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = "'" & myCell.Formula
Next myCell
End Sub

Sub TransformToFormula()
Dim myCell As Range
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
End Sub
 
D

David McRitchie

Hi Bernie, and Daniel (original poster),
That won't quite work because copying and pasting will
modify/adjust addresses in formulas for the new location.
(post was clear on using ranges).

A20: 36599
B20: =AND(A20>DATE(1999,1,1),A20<DATE(2100,1,1))

If the formula in B20 is copied to F20
F20: =AND(E20>DATE(1999,1,1),E20<DATE(2100,1,1))

One way to solve would be to use GetFormula
F20: =Personal.xls!GetFormula(B20)
use fill handle to copy down for a range, and then
convert them to text. Ctrl+C, Paste Special, Value
or using your FormulaToText macro.

A potentially better solution, but the current macros only
does one cell, not a range:

Chip Pearson wrote macros to copy the formula to clipboard
unchanged, and to paste from clipboard unchanged. Then
you could run your FormulaToText. I expect Chip's macros
could be be modified for a range instead of a single cell.
I use them in a right-click menu as he suggested:

Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Working with the Windows Clipboard -- Chip Pearson
http://www.cpearson.com/excel/clipboar.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
B

Bernie Deitrick

David,

The copy is performed on strings, not formulas, so they are not
modified/adjusted in any way.

HTH,
Bernie
MS Excel MVP

David McRitchie said:
Hi Bernie, and Daniel (original poster),
That won't quite work because copying and pasting will
modify/adjust addresses in formulas for the new location.
(post was clear on using ranges).

A20: 36599
B20: =AND(A20>DATE(1999,1,1),A20<DATE(2100,1,1))

If the formula in B20 is copied to F20
F20: =AND(E20>DATE(1999,1,1),E20<DATE(2100,1,1))

One way to solve would be to use GetFormula
F20: =Personal.xls!GetFormula(B20)
use fill handle to copy down for a range, and then
convert them to text. Ctrl+C, Paste Special, Value
or using your FormulaToText macro.

A potentially better solution, but the current macros only
does one cell, not a range:

Chip Pearson wrote macros to copy the formula to clipboard
unchanged, and to paste from clipboard unchanged. Then
you could run your FormulaToText. I expect Chip's macros
could be be modified for a range instead of a single cell.
I use them in a right-click menu as he suggested:

Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Working with the Windows Clipboard -- Chip Pearson
http://www.cpearson.com/excel/clipboar.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm



Bernie Deitrick said:
I _think_ this will solve your problem.
Select the range you want to copy, run the first macro below, copy the
range, do the paste on the new range, then run the second macro while you
still have the paste area selected.

Sub FormulaToText()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = "'" & myCell.Formula
Next myCell
End Sub

Sub TransformToFormula()
Dim myCell As Range
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
End Sub
Click to expand...
this -- Daniel
Click to expand...
 
D

David McRitchie

Ok I see part of what you suggested was converting what you
have to text and then back to formulas. Actually as text
that opens up additional possibilities for modifications.
 
Top