How to Copy Formulas not Values ...

[Ronnie]

Hi Excellences!

I want to copy one row to another row,
I'm interested in copying only Excel FORMULAS not VALUES.
I made PasteSpecial -> Paste -> Formulas
and it turned out that VALUES of copied row
are pasted into a new row.
As I mentioned I want only FORMULAS on the new row.
I tried also to write a macro but it was no success.

How to do it?

Any help will be greatly appreciated!

Best regards

Ronnie

 

[Tom Ogilvy]

Sub CopyFormulas()
Dim rng As Range, cell As Range
Dim rw As Long
rw = 5
On Error Resume Next
Set rng = Rows(1).SpecialCells(xlFormulas)
On Error GoTo 0
if not rng is nothing then
For Each cell In rng
cell.Copy
Cells(rw, cell.Column).PasteSpecial xlFormulas
Next
End if
End Sub

is one way. As written, copies from Row 1 to Row 5. If you wanted to copy
from Row 1 to the row with the activeCell

Sub CopyFormulas()
Dim rng As Range, cell As Range
Dim rw As Long
rw = ActiveCell.Row
On Error Resume Next
Set rng = Rows(1).SpecialCells(xlFormulas)
On Error GoTo 0
If not rng is nothing then
For Each cell In rng
cell.Copy
Cells(rw, cell.Column).PasteSpecial xlFormulas
Next
End if
End Sub

 

[Ronnie]

Thank you voey much for help!

Best regards

Ronnie