Inserting new row below active cell

Question

I would like to insert a new row below the current active cell and copy down the contents of column A of the row above the newly inserted. (I will be assigning this to a ribbon option)

Exp: If cell E7 is active. When the macro is run, it will insert a new row below that row and copy the contents of A7 to A8. These cell selections will be random. But the new row must always be below the active cell and only copy-down contents in column A.

Answer 1

Hi,

try this..

(make a copy before..)

Sub InsertRow()

r = Selection.Row

Cells(r + 1, 1).EntireRow.Insert

Cells(r, 1).Copy Destination:=Cells(r + 1, 1)

End Sub

Answer 2

Hi

This was allready very helpful. Now I would like to insert data in this new row

something like this (but this doesn't work):

Dim newrow As ListRow

r = Selection.Row

Cells(r + 1, 1).EntireRow.Insert

Set newrow = Cells(r + 1, 1)       <=This is where my problem is located!!

With newrow

    .Range(2) = Me.tbBand

    .Range(3) = Me.tbTitle

    .Range(4) = Me.tbLabel

    .Range(5) = Me.tbReleaseDate

    .Range(6) = Me.tbCode

    .Range(7) = Me.tbDatePurchased

    .Range(8) = Me.tbGenre

    .Range(9) = Me.tbLand

    .Range(10) = Me.tbExtraInfo

End With

Would be very thankful if there is a simple solution for this matter.

Greetz,

Bart

Answer 3

Using this method inserted cells accept formatting from row below (NOT above, despide setting CopyOrigin to xlFormatFromLeftOrAbove)

' I inserting new row at the end of the table before last line with sums etc.

' Inserted row is formatted exactly as whole table, not the last row.

' MovieNum - named range of first column (eg. A2:A43)

Dim Rng as Range ' Needed to restore selection after Paste

With Range("MovieNum")

  .Cells(.Count, 1).EntireRow.Insert Shift:=xlDown, _

    CopyOrigin:=xlFormatFromLeftOrAbove

  .Cells(.Count, 1).EntireRow.Copy

  Set Rng = Selection

' Never use Paste method if you have Conditional formatting, this will broke ranges

  .Cells(.Count - 1, 1).EntireRow.PasteSpecial _

    Paste:=xlPasteFormulasAndNumberFormats

  Rng.Select

  Application.CutCopyMode = False

  .Cells(.Count, 1).EntireRow.ClearContents

End With

Can anyone suggest an easier way?

Answer 4

Simple & reliable, yet Powerful. This is perfect and works great... Thanks for the rapid response...