Why does this code not do what I want

[Patrick C. Simonds]

Can anyone tell me why this code not only pastes the Formulas (which I want)
but also the values in the cells?


Sub test()

Dim cellActive As Range
Set cellActive = ActiveCell


Dim rgNewRow As Range
ActiveCell.EntireRow.Insert Shift:=xlDown
Set rgNewRow = Rows(cellActive.Row - 1)


cellActive.EntireRow.Copy

rgNewRow.PasteSpecial (xlPasteFormulas)


End Sub

 

[Mike H]

Patrick,

Because code is like that it does what you tell it to do which is frequently
not what you want it to do. Yo're using paste-special paste-formula and what
excel does in that case is paste the formula and not the value. But, if you
copy a constant and tell it to past the formula which is what you were doing
it simply pastes the constant. Try it manually on a worksheet. You need a
different approach so try this:-

Sub test()
Dim cellActive, myrange As Range
Set cellActive = ActiveCell
Dim rgNewRow As Range
ActiveCell.EntireRow.Insert Shift:=xlDown
Set rgNewRow = Rows(cellActive.Row - 1)
cellActive.EntireRow.SpecialCells(xlCellTypeFormulas).Select
Selection.Copy
Set myrange = Selection
For Each c In myrange
c.Offset(-1, 0).Formula = c.Formula
Next
End Sub

Mike

 

[Mike H]

Hmm!

Try this instead

Sub test()
Dim cellActive, myrange As Range
Set cellActive = ActiveCell
Dim rgNewRow As Range
ActiveCell.EntireRow.Insert Shift:=xlDown
Set rgNewRow = Rows(cellActive.Row - 1)
Stop
cellActive.EntireRow.SpecialCells(xlCellTypeFormulas).Select
Set myrange = Selection
For Each c In myrange
c.Copy
c.Offset(-1, 0).PasteSpecial (xlPasteFormulas)
Next
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

With the Stop statement removed I would assume.<g>

I think your first macro might have been closer to what is wanted than this
one. Try using a formula involving a range (such as SUM)... your first macro
retained the same range as the old row, this new one doesn't. In other
words, the first macro produced an exact copy of the original row (which I'm
guessing, but am not completely sure, is what the OP wanted); this one
changes the range during the "copy".

Rick

 

[Mike H]

Yes it would make for more seamless execution to remove the Stop <g>

I wasn't sure either what the OP wanted so I went back and checked the
original code and noted that it changed the range, hence my second attempt.

Mike

 

[Rick Rothstein \(MVP - VB\)]

Yeah, I wasn't sure either (and still am not)... but I figured his subject
line ruled out that the code was doing exactly what he wanted... plus it is
unclear to me that the OP knows the formulas are changing the ranges on him.

Rick