CODEWITHSUNDEEP
excelvba
Gary's Student
Gary's Student

Reputation: 96791

Insert Row without Pasting

I am trying to debug the following small macro:

Sub InsertRowAbove()
'
' InsertRow Macro
' Pushes active row down
'
    Dim N As Long
    ActiveCell.EntireRow.Insert Shift:=xlDown
    N = Cells(Rows.Count, "G").End(xlUp).Row
    Range("G4").Copy Range("G5:G" & N)
    Range("J4").Copy Range("J5:J" & N)

End Sub

The macro should allow the user to insert an empty row directly above the active cell and fill down formulas in columns G and J.

The macro works.

If, however, the user performs a copy/paste operation just before running the macro, the macro will attempt to also paste material into the new row.

We want to macro to leave the newly inserted row empty.

Any suggestions??

Upvotes: 0

Views: 1792

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

Add a line to disable CutCopyMode:

Sub InsertRowAbove()
'
' InsertRow Macro
' Pushes active row down
'
    Dim N As Long

    Application.CutCopyMode = False 'This line to get rid of clipboard.

    ActiveCell.EntireRow.Insert Shift:=xlDown
    N = Cells(Rows.Count, "G").End(xlUp).Row
    Range("G4").Copy Range("G5:G" & N)
    Range("J4").Copy Range("J5:J" & N)

End Sub

Upvotes: 1

Related Questions