copy formulas, not values

[Mark Kubicki]

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

 

[Jim Thomlinson]

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"

 

[Rick Rothstein]

Here is another macro for you to consider...

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

 

[Jim Thomlinson]

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(xlCellTypeFormulas)
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

 

[Rick Rothstein]

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.

 

[Jim Thomlinson]

No need to say sorry. Don't avoid posting just becuase I have answered. I
always like to see someone elses take on the same problem. And I am the first
to admit when I prefer someone elses code. Yours just feels a little cleaner.

My guess is that speed would depend on the number of constanst in the row.
More constants mean that my code has more to copy and delete. If there are
very few constants but lots of formulas then your loop will slow you down.
That is all academic though since the code probably executes faster than the
screen can refresh.