copy\paste with vba
 

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

--------------------------------------------------------------------------


---
Message posted from http://www.ExcelForum.com/

copy\paste with vba
 

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.

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).ClearC ontents
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



In article ,
polletje wrote:

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

--------------------------------------------------------------------------

copy\paste with vba
 

polletje,
Just throw away the ActiveSheet.Paste-line


---
Message posted from http://www.ExcelForum.com/

copy\paste with vba
 

Thanx,

the quick fix did the trick!!!


---
Message posted from http://www.ExcelForum.com/