Excel - Insert row macro to include table format

Question

Currently in my excel spreadsheet I am not able to insert a row below my selected row. New tickets are added to the end of the spreadsheet so when I want to add a new ticket I click the row before the TOTALS row and click insert but it enters the new row above the selected row.

Screenshot here shows I selected cell 33 and clicked insert but the new row was added above:

When I use the macro code it adds the row perfectly:

BUT, the new row is not formatted to match the table. I would want to adjust my macro to format the new row based off the table format (in this case it should have been highlighted in blue and have the correct lines). The double upper cell lines should stick to the total row as well.

Here is my mecro below:

Sub InsertRowBelow()

    Application.ScreenUpdating = False

    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.EntireRow.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

Please let me know if I am not clear. Any tips/guides/tutorials would be helpful.

When I'm running your code it works perfectly, it adds the row and keeps the Table format. — Shai Rado, Jun 27 '16 at 17:44
I also suggest [avoiding using `.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), which could cause headaches. — BruceWayne, Jun 27 '16 at 17:48
it does add the format for me too but not if I select the row RIGHT before the totals row. If I select a row in-between the spreadsheet it works flawlessly. When I am adding to the end of the spreadsheet (above the TOTALS row) , it does not keep format. — LOZ, Jun 27 '16 at 17:51

score 1 · Accepted Answer · answered Jun 27 '16 at 17:59

1

If you just want to add a new row to the table then use:

ActiveWorkbook.Worksheets(1).ListObjects(1).ListRows.Add

answered Jun 27 '16 at 17:59

Brian

2,078
1
15
28

This worked better, but It did not apply lines (borders) in-between each cell. Only applied lines (borders) below and above the row. – LOZ Jun 27 '16 at 18:16
@KKP It should apply the table's format...so I'm not sure. Did you add formatting to it yourself? – Brian Jun 27 '16 at 18:20
this might be a big possible. let me check and get back to you. – LOZ Jun 27 '16 at 18:23
This was the problem. I started the spreadsheet from scratch and solved my problem. Thank you! – LOZ Jun 27 '16 at 18:46

Excel - Insert row macro to include table format

1 Answers1