Paste Special prob

[camlad]

Having copied a row I need to paste ONLY the formulas which occur in some
cells into another row. I want to leave the other cells empty and not paste
in the values.

I asked a similar question earlier (12/11/2008 3:41) but did not get a
satisfsactory answer - I only want the formulas pasted into the new row.

Any suggestions?

 

[Otto Moehrbach]

Copy and paste the whole row, constants, formulas, everything. Then select
the row you just pasted. Hit the F5 key. Select Special - Constants. This
selects only those cells that have constants. Hit the Delete key. HTH
Otto

 

[camlad]

Many thanks Otto - you pointed me in the right direction to write this macro
which is what I wanted to recreate, having lost it recently:

Sub InsertBlankRowIncFormulas()
Application.ScreenUpdating = False
'Insert row above selected cell
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
'Clear cell colors
Selection.Interior.ColorIndex = xlNone
'Clear contents of cells
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
'Clear any comments
ActiveCell.Rows("1:1").EntireRow.Select
Selection.ClearComments
'End in Name cell
Cells(ActiveCell.Row, 2).Select
End Sub