VBA Code to Copy Row, Paste Row Below Last Row

[BettyBoop0916]

Hi there,

I'm having difficulty in trying to come up with a VBA code for a macro button that should, once pressed, copy the entire row, paste row below that row, delete all data but keep existing formulas (and outlines if there are any).

This is what I've come up with:

Private Sub CommandButton1_Click()
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert
Selection.SpecialCells(xlCellTypeConstants, 3).Select
Selection.ClearContents
Application.CutCopyMode = False

This works in the sense that it inserts the row and deletes the data and keeps the formula, HOWEVER it does not insert it below the last row, but above.

What needs to be done to make this row insert below and not above.

I tried this:

Private Sub CommandButton1_Click()
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert shift:=xlDown
Selection.SpecialCells(xlCellTypeConstants, 3).Select
Selection.ClearContents
Application.CutCopyMode = False

But it still did not insert the row below.

Anybody have any suggestions?

This is for my office computer at work...they are running Microsoft Office 2003.

 

[VoG]

Perhaps

Private Sub CommandButton1_Click()
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Offset(1).Insert
Selection.SpecialCells(xlCellTypeConstants, 3).Select
Selection.ClearContents
Application.CutCopyMode = False

 

[BettyBoop0916]

Thanks for the reply...

But, it still didn't work, and I have tried to do that as well, but still end up having the row insert above and not below. It's very strange...in all the forums I've looked at, they all say the same thing.

But I still end up with it not working.

Anything else you can suggest?

 

[VoG]

This works for me

Sub btest()
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Offset(1).Insert
Selection.Offset(1).Select
Selection.SpecialCells(xlCellTypeConstants, 3).Select
Selection.ClearContents
End Sub

 

[BettyBoop0916]

Thanks so much! It works perfectly.

Seems so simple now lol.

Thanks!