How to Maintain Formulas of Copy and Cut

[senators40]

I would like to use the properties of cut for a formula while still maintaining the original place where the formula resided.

For instance if the formula is in cell A3 =Sum(A1:A2)

I want to move it to cell B3 and have the formula in B3 be =Sum(A1:A2) and still have cell A3 have =Sum(A1:A2)

I know you can use absolutes (with the $) to do it but sometimes the formulas are more complex

Manually I insert an apostrophe ' in front, then copy then paste to another cell and remove the apostrophe from both items. It works but I would like to know if there is a quicker way to do this through a macro.

Any help would be appreciated

Thanks,

Jeff

 

[SkipVought]

Did you try macro recording what you want to do?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Spooku777]

The easiest way without a macro is to Copy the formula text and paste it...
1. Double click the cell with the formula you want (eg A3)
2. Select the formula text
3. Copy it (Ctl+C, or right-mouse click and copy)
4. Paste it to your destination cell (B3)

But if you really want to use VBA then it's simply:
Range("B3").Formula = Range("A3").Formula

 

[xlbo]

Did you know you can use F4 to switch between the absolute and relative references. To be honest, you are far better off doing this then trying to cut/copy and paste in this way - far less prone to error....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

For frequent use you can automate Spooku's second tip and create hotkey:

Sub CopyFormula()
Application.InputBox("Select target range", "Copy formula", , , , , , 8).Formula = Application.InputBox("Source range", "Copy formula", , , , , , 8).Formula
End Sub

I would rather try to redesign worksheet/calculations to avoid repeating the same formula.

combo