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(xlCellTypeFormulas)
C.Copy C.Offset(1)
Next
End Sub

--
Rick (MVP - Excel)


"Mark Kubicki" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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(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

--
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(xlCellTypeFormulas)
> C.Copy C.Offset(1)
> Next
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "Mark Kubicki" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:33F0A77B-8DB6-4B39-AAD6-(E-Mail Removed)...
> 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
>
> --
> 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(xlCellTypeFormulas)
>> C.Copy C.Offset(1)
>> Next
>> End Sub
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Mark Kubicki" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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
>> >
>> >
>>
>>

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
> news:33F0A77B-8DB6-4B39-AAD6-(E-Mail Removed)...
> > 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
> >
> > --
> > 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(xlCellTypeFormulas)
> >> C.Copy C.Offset(1)
> >> Next
> >> End Sub
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "Mark Kubicki" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >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
> >> >
> >> >
> >>
> >>
>
>