Adding a new row with vba that combines formatting

[LaffyAffy13]

Hello,

I am trying to use VBA to insert a new row. I have conditional formatting for a group of my cells (M19:FL30), and I want the new line to be inserted within this group of cells. Currently, I have this, which works fine:

Sub new_line()
    Rows("8:8").Select
    Selection.Copy
    Rows("27:27").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

As you can see, I have copied row 8 and pasted it into row 27. However, row 8 has different conditional formatting than my group of cells (M19:FL30). Is there a way to combine the two so that my inserted row has the coniditional formatting of both row 8 and the group of cells (M19:FL30)?

[Cutter]

@ LaffyAffy13

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules (Rule #3) require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. Instructions on how to apply them can be viewed by clicking the Forum Rules button at the top of the page and seeing rule #3.
Thanks.

[LaffyAffy13]

Okay thanks, sorry about that.

[JBeaucaire]

Combine the two? No, not very practical. You need to decide which formatting will remain.

[LaffyAffy13]

Okay, but even if I keep the old formatting from line 8, the formatting from (M19:FL30) is broken up into two pieces, which is why I wanted to see if you could just combine them and just have additional formatting for the new line 27. Sorry if that wording doesnt make sense. Is there any way to maybe just insert the new line without breaking up the formatting for (M19:FL30)?

[JBeaucaire]

"Breaking up the formatting" is a term I don't understand out of context. Is there something you're going to show us at some point?

[LaffyAffy13]

What I mean is the formatting for (M19:FL30) is shown as "$M$19:$FL$26, $M$28:$FL$30" after I add the new row and if I keep adding new rows in areas other than line 27, the formatting "breaks" up again and forms more complex and long wording that slows me down. I want to stop these "breaks" in the syntax. Does that make more sense?

[LaffyAffy13]

Okay so I think I found a better way to word this. If I copy my row 8 and insert it into row 27, and I want my copied row 8 to have the same conditional formatting as all of the rows in my section M19:FL30, is there a way to do this with visual basic? And if there is, is it possible to keep the syntax for the conditional formatting simple (e.g. $M$10:$FL$17,$M$19:$FL$31,$M$33:$FL$41) instead of complex (e.g. =$M$8:$FL$8,$M$10:$FL$17,$M$19:$FL$26,$M$28:$FL$32,$M$34:$FL$42) every time I insert a row? And sorry, I can't show you my file because my work computer is set to keep anyone from sharing files on the internet (probably to keep our projects from reaching competitors). I'm also only an intern so I'm not really used to this. Thanks to anyone and everyone who posts their answers, I could really use the help.

[JBeaucaire]

If you are inserting rows causing the destination table to expand, so there are more rows after than before, then you can copy/insert the data, then copy the formatting from the row above and reapply it to the inserted row.


If you're not really INSERTING a row, just copying into an existing row, then when you paste into that existing row, instead of paste, use Paste Special > Values

[LaffyAffy13]

Thank you so much, the first answer helps me out a little. The only problem is that I need to use a macro to do this and I don't believe that you can change conditional formatting with Visual Basic. Can you?

[JBeaucaire]

If you COPY > PASTE SPECIAL > FORMATS, that only transfers the cell's formatting from one to another, including conditional formatting.

[LaffyAffy13]

When I tried to record a macro and copy and insert the row manually, I clicked PasteSpecial and then chose Formulas, and the formatting from my original row was kept. Thank you for that. However, the content of this copied row has now been deleted. Is there a way to paste formulas and content? Also, I still face the primary problem of having my range split by this inserted row. So I set the formatting of my row 8 to be the same as my other rows (everything looks the same, except for the content). That way it appears as though only a new row has been inserted, and every row has identical formatting. But this new row still splits my range into two separate pieces. I will create an example excel file (since this is so complicated to explain, I'm really sorry. Thanks for all of your help, btw) when I have the time this weekend and send it from my laptop, as I cannot do that here at work. Thanks again, I will get back to you as soon as I can. Sorry for the confusion.

[JBeaucaire]

Copy a row that has the correct formatting (perhaps the row above the one you inserted) and then paste special > format back over the inserted row.