VBA - insert new row Y, copy row X and paste format and formulas in row Y

[emma1234]

Hello
Sorry this could be really simple but I've spent hours and feel like I'm going round in circles! Any help would be really appreciated.

I want a macro to insert a new row below the current active cell, then copy and paste format and formula from row 8 into that new row. I have been trying the below;

Sub Insert_Row()


ActiveCell.EntireRow.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("8:8").Select
Selection.Copy
ActiveCell.EntireRow.Select
ActiveSheet.Paste

End Sub

When I do it though, it inserts the new row but not the formulas. It also ends with highlighting row 8 - I want it to either highlight all the new row, or just the B cell in the newly created row.

Please help!

Thanks

 

[DanteAmor]

Try this

Sub Insert_Row()
    ActiveCell.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("8:8").Copy Range("A" & ActiveCell.Row)
End Sub

 

[emma1234]

Thanks so much for coming back to me. I've tried your suggestion but it's not pasting the formulas still - any other ideas?

 

[emma1234]

Ignore me - it is working! thank you so much I really appreciate it

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.