How to Copy Formulas not Values ...

  • Thread starter Thread starter Ronnie
  • Start date Start date
R

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
 
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
 
Back
Top