Copy Formula without changing cell refence

[Agustus]

Hi,

I modified this macro from earlier post fom Tom Ogilvy, but it is doing
exactly what it should be. I like to copy the formula in a selected
cell(s) and paste them in another cell without changing the cell
reference. Can someone help, please?

Thank you in advance.

Agustus

Sub CopyFormulas1()
Dim rng1 As Range, rng2 As Range, i As Long
On Error Resume Next
Set rng1 = Selection.CurrentRegion
' original post had this: Set rng1 = Application.InputBox("Select cells
to copy using mouse", Type:=8)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "You selected nothing"
Exit Sub
End If

On Error Resume Next
Set rng2 = Application.InputBox("Select top cell to paste using mouse",
_
Type:=8)
On Error GoTo 0
If rng2 Is Nothing Then
MsgBox "You selected nothing"
Exit Sub
End If

i = 1
For Each cell In rng1
rng2(i).Formula = cell.Formula
i = i + 1
Next

End Sub

 

[Agustus]

Sorry, typo..

I modified this macro from earlier post fom Tom Ogilvy, but it is Not
doing
exactly what it should be. I like to copy the formula in a selected
cell(s) and paste them in another cell without changing the cell
reference. Can someone help, please?

 

[Agustus]

Gary''s Student,
Thank you for your reply. Even though it is not what I had intended in
mind as the destination range needed to be dynamic, but you've given me
ideas for the next steps.

Regards,
Agustus

 

[Guest]

Update this post if you require anthing else.

 

[cathellisuk]

Agustus's copy problem interested me as it's something I need to do
myself on occasion. I have been exploring a few ways to copy formulae
without changing the cell references using the keyboard (or mouse if
you prefer).
I know this is an Excel programming group but sometimes the good old
keyboard can be useful.
I'm using Excel version 2002.

Here are a couple of solutions someone might find useful.


1. Copying formulae from a range of contiguous cells. (eg D5 to
G10) without changing the cell references in the formulae.

a) open up a text editor ( Notepad , Word . . .whatever you like)
b) In Excel change the view option on the worksheet to show cell
formula rather than values.
The quickest way to do this is to press Ctrl + ` (that's a
single left quotation mark)
This key combination toggles the display between value and
formula view.

c) Select the cells you want to copy the formulae from
d) Press Ctrl+C (to copy to the clipboard)
e) Swap to your text editor
f) Press Ctrl + V to paste the formulae.
g) In the text editor select the text you have just pasted
h) Press Ctrl + C to copy it
i) Back in Excel select the top left hand cell of the range you want to
paste into
j) Press Ctrl + V to paste the formulae
h) Press Ctrl + ` again to put Excel back in view "values" mode. This
step can be done after step (d) if you prefer.

I was surprised at how well this copy method works. Even if the source
range you have copied contains a number of blanks cells Excel still
pastes the text in the clipboard into the right target cells.

2. Copying a formula from a "single" cell without altering the formula

The trick here is to copy the formula FROM THE FORMULA BAR rather than
from the source cell. This can be done by :
a) select the cell you want to copy
b) In the formula bar select all text that makes up the formula
c) Press Ctrl + C to copy to the clipboard
d) select the cell you want to paste into
e) Press Ctrl + V to paste the formula

Cath