Insert Row, Copy Format/Formulas

[Rockee052]

Hello,

xl2003

I have been trying to figure out how to copy formulas when adding rows
I can add a row and copy the format just not the formulas in th
columns. So, I can add a new row via VBA but, I cannot copy th
formulas. Is there a way the code can look above the row where th
active cell is and if there is a formula in the row above, it woul
copy it in the new row added. There would be some occasions where ther
would be no formula in the row above the active cell. I have recorded
macro and played around with the code but I keep on getting errors.

Here is what I have so far:

Sub AddRow()
Dim rng As Range
Set rng = Selection
Selection.EntireRow.Insert
Selection.Offset(-1, 0).EntireRow.Copy
Selection.EntireRow.PasteSpecial (xlFormats)
Application.CutCopyMode = False
rng.Select
End Sub

Thanks for any help

Rockee Freema

 

[Tom Ogilvy]

click in a cell with a formula in the cell above. Do ctrl+D

turn on the macro recorder, now do it. You get selection.Filldown

you could do

Rows(3).filldown
rows(3).SpecialCells(xlconstants).Clear

That copied all the cells down to row 3 from row 2, then deletes the
constants so only the formulas remain.

 

[Rockee052]

Tom,

Thanks for your reply. I did run a macro and saw that. I think I wa
adding to much of what the macro recorded (if that makes sense).

That should do the trick.

Thanks

Rocke