Rick. You forgot to insert a row below the acitve row and on the off chance
that the row does not contain any formulas you get a 1004 error. The best
code would probably be more like your code than mine. It should check the
current row for formulas. If there are some then insert a blank row beneath
and then copy. Something like this...(untested)

Sub CopyFormulasOnly()
Dim C As Range
Dim rngFormulas as range

on error resume next
Set rngFormulas = ActiveCell.EntireRow.SpecialCells(xlCellTypeFormul as)
on error goto 0

if rngformulas is nothing then
msgbox "No formulas to copy"
else
activecell.offset(1,0).entirerow.insert
For Each C In rngFormulas
C.Copy C.Offset(1)
Next C
end if
End Sub

--
HTH...

Jim Thomlinson


"Rick Rothstein" wrote:

Here is another macro for you to consider...

Sub CopyFormulasOnly()
Dim C As Range
For Each C In ActiveCell.EntireRow.SpecialCells(xlCellTypeFormul as)
C.Copy C.Offset(1)
Next
End Sub

--
Rick (MVP - Excel)


"Mark Kubicki" wrote in message
...
I have this very simple code behind a command button on a worksheet; but it
is not behaving as i want it to...
The portion that says: ...PasteSpecial Paste:=xlPasteFormulas,
ought to copy only the formulas, not any of the values; however, it is
copying all (as woudl a simple "paste")

Any suggestions will be greatly apreciated,
Mark



Private Sub cmdInsertRow_Click()
ActiveSheet.Unprotect Password:="TDA"
TargetRow = ActiveCell.Row
Rows(TargetRow).Offset(1, 0).Insert shift:=xlDown
Range(TargetRow & ":" & TargetRow).Copy
Range(TargetRow + 1 & ":" & TargetRow + 1).PasteSpecial _
Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
ActiveSheet.Protect Password:="TDA"
End Sub