copy formulas, not values
 

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

copy formulas, not values
 

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

copy formulas, not values
 

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

copy formulas, not values
 

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

copy formulas, not values
 

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

copy formulas, not values
 

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.
--
HTH...

Jim Thomlinson


"Rick Rothstein" wrote:

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