Using Macro to copy row formatting

[jjury75]

Good afternoon,

I'm not too advanced in excel. I developed a form that other people to complete. I need to have an option for them to add a line that will automatically format the same as the line above or below it, including some cells being merged and others having drop down lists. So far, I've been able to create a Macro button that will insert the row with all the same features as the others, however, it does not merge the cells that need to be merged. Here is the code I used to run the macro:

[FONT=&quot]Sub InsertRowFormulas()[/FONT]
[FONT=&quot]Application.ScreenUpdating = False[/FONT]
[FONT=&quot]Dim cell As Range[/FONT]
[FONT=&quot]Selection.EntireRow.Insert[/FONT]
[FONT=&quot]For Each cell In Intersect(ActiveSheet.UsedRange, Selection.Offset(-1, 0).EntireRow)[/FONT]
[FONT=&quot]If cell.HasFormula Then[/FONT]
[FONT=&quot]cell.Copy cell.Offset(1, 0)[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot]Next[/FONT]
[FONT=&quot]Application.ScreenUpdating = True[/FONT]
[FONT=&quot]End Sub

Is there something I could add to make the the cells merge as well?[/FONT]

 

[JoeMo]

This snippet should insert a partial row (the width of the selection) and transfer all the formulas and formats(including merged cells) of the initial selection to the inserted partial row.

Selection.Insert shift:=xlDown
Selection.Offset(1, 0).Copy
Selection.PasteSpecial Paste:=xlPasteAll

EDIT: If you want to insert an entire row just change the first line to:
Selection.EntireRow.Insert

 

[jjury75]

I replaced the code, but it didn't work.

Would the code you suggested require the end user to highlight the entire selection they wanted to insert? I would like for the end user to just click the button, and a new row would insert with the formatting, etc. Is there a way to do that?

 

[JoeMo]

It's not a selection until it is all highlighted . You used Selection in your OP so I thought that's what you wanted. If you want to apply it to a specific range (in a single row), like say J11:P11 then repalce Selection with Range("J11:P11") throughout the snippet I posted.

 

[jjury75]

Thanks JoeMo. I don't actually understand the code. I copied and pasted it from what I found online. At least now I know what "selection" means in code.