Insert new row below and copy format

[brayleyboy]

Can anyone help me with a small piece of code?

Here is what I need to do.

A user will select a cell or range of cells. I would like a macro which when run would insert a new row below the slected cell(s) and copy the format of the row above the new one into the new row.

Thanks
David

 

[onlyadrafter]

Hello,

I Know select is bad, I just cant think of a way round it at the moment.

Try this - untested really!

Sub copy_from_above()
ActiveCell.Offset(Selection.Rows.Count, 0).Select
Selection.EntireRow.Insert
Rows(ActiveCell.Row).Offset(-1, 0).Copy
Range("A" & ActiveCell.Row).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False
End Sub

I dont think you really need these lines as it seems to copy the line above anyway

Rows(ActiveCell.Row).Offset(-1, 0).Copy
Range("A" & ActiveCell.Row).PasteSpecial (xlPasteFormats)

Actually try

Sub copy_from_above()
ActiveCell.Offset(Selection.Rows.Count, 0).EntireRow.Insert
End Sub

or even

Sub copy_from_above()
ActiveCell.Offset(Selection.Rows.Count, 0).EntireRow.Insert
Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy
Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False
End Sub

again I don't think you need these

Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy
Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False

 

[brayleyboy]

Thanks a lot. This one seemed to work perfectly

Sub copy_from_above() 
ActiveCell.Offset(Selection.Rows.Count, 0).EntireRow.Insert 
Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy 
Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats) 
Application.CutCopyMode = False 
End Sub