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

PasteFormulas is a little misleading. It copies and pastes all of the cells.
Where a cell contains a formula it pastes the formula. If it is a contant it
pastes the constant. It does not paste any of the formatting or such. Try
this...

Me.Unprotect Password:="TDA"
With ActiveCell
Rows(.Row).Offset(1, 0).Insert Shift:=xlDown
Rows(.Row).Copy Destination:=Rows(.Row + 1)
On Error Resume Next
Rows(.Row + 1).SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
Me.Protect Password:="TDA"

--
HTH...

Jim Thomlinson


"Mark Kubicki" wrote:

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

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

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

First off, I didn't mean to post my message against yours... I thought I was
posting it against one of the OP's messages. Sorry.

Second, you are absolutely right... those are good suggestions.

Third, I don't really have a feel for which is faster... my code is using a
loop (but only through items that need to be copied) whereas yours is
deleting (without a loop) cells that do not contain formulas. I just figured
I would offer the alternative for the OP to choose from.

--
Rick (MVP - Excel)


"Jim Thomlinson" wrote in message
...
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