copy\paste with vba

  • Thread starter Thread starter polletje
  • Start date Start date
P

polletje

I created a macro to copy and paste the formulas from one row to
another. I dont want to copy the the values.
When I run it, it copys the formulas but ALSO the values. What am i
doing wrong? Can someone help me?

-------------------------------------------------------------------------
Application.Goto Reference:="totaal"
Selection.EntireRow.Insert
Application.Goto Reference:="totaal"
ActiveCell.Offset(-2, -35).Range("A1:AJ1").Select

Selection.Copy
Application.Goto Reference:="totaal"
ActiveCell.Offset(-1, -35).Range("A1:AJ1").Select

Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
 
From XL/VBA Help (Formula Property)
If the cell contains a constant, this property returns the constant. If the
cell is empty, Formula returns an empty string.
Click to expand...

Paste Special conforms to that definition.

A quick fix might be to add this right after the paste special:

On Error Resume Next 'in case no constants
Selection.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0


But you may want to take a look at David McRitchie's INSRTROW()
macro, too:

http://www.mvps.org/dmcritchie/excel/insrtrow.htm
 
Back
Top