Search code examples
excelexcel-2010excel-2007vba

Excel - Insert row macro to include table format

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:

enter image description here

enter image description here

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

enter image description here

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.

Solution

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

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