Copy Formula from Sheet2 to Sheet1

[Nick70]

Hi,

I would like to copy a formula from Sheet2 to Sheet1 so that the references do not change.

For example I have formula

=IF(AND(E$3>=$C5,E$3<=$C5),$D5,"")

in cell E5 of Sheet2.

If I copy this formula to cell G8 of Sheet1 using code

Worksheets("Sheet2").Activate
    Range("E5").Select
    Selection.copy
    Sheets("Sheet1").Select
    Range("G8").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

the corresponding formula that I will get in cell G8 of Sheet1 will be

=IF(AND(G$3>=$C8,G$3<=$C8),$D8,"")

.

I would like to amend the code so that formula that is copied in cell G8 of Sheet1 remains

=IF(AND(E$3>=$C5,E$3<=$C5),$D5,"")

so does not change references.

How would I do that?

Thanks,
Nic

 

[DanteAmor]

Try:

Sub copy_formula()
  Sheets("Sheet1").Range("G8").Formula = Sheets("Sheet2").Range("E5").Formula
End Sub

But your formula is redundant. A value cannot be less than value X and also be greater than value X.

So the formula should be like this:

=IF(E$3=$C5,$D5,"")

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Nick70]

Great thanks and yes the formula had a mistake
Nic