VBA Excel - Insert table row with same formatting as row above it

Question

I have a button that users can click which adds a row to a given table on a given sheet. If the user has selected a cell within the table, then the macro determine it needs to add a row directly below the selection. If the user is not in the table, then it simply adds a row to the bottom of the table.

It works rather well except I can't figure out how to copy formatting from the row above. Is this possible?

My code which may be of use is below:

Private tblTotalRows As Integer
Public selectedRow As Integer
Public selectedCol As Integer

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add a row to the table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub addRow(shtName As String, tblName As String, startRow As Integer)

    Dim tableRef As Integer

    Call getSelectedCell
    Call totalRowsInTable(shtName, tblName, startRow) ' Sets the selectedRow and tblTotalRows property

    ' We determine the row number where the new table row should be placed
    tableRef = selectedRow - startRow + 1

    ' Check to make sure the user is in the active table and then add a row
    If tableRef > 0 And selectedRow <= tblTotalRows Then
        Sheets(shtName).ListObjects(tblName).ListRows.Add (tableRef)
    Else
    ' If they're not in the table and then add a row to the bottom of the table
        Sheets(shtName).ListObjects(tblName).ListRows.Add AlwaysInsert:=True
    End If

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Get the row and column of the cell under selection
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub getSelectedCell()

    selectedRow = ActiveCell.Row
    selectedCol = ActiveCell.Column

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Count the number of rows in the Table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub totalRowsInTable(shtName As String, tblName As String, startRow As Integer)

    Call getSelectedCell

    ' Select the entire table
    Sheets(shtName).ListObjects(tblName).Range.Select

    ' Count the number of rows in the table and add to the starting row
    tblTotalRows = Selection.Rows.Count + startRow - 1

    ' Go back to the users position
    Cells(selectedRow, selectedCol).Select

End Sub

Does this help http://stackoverflow.com/questions/8184373/excel-vba-insert-row-with-formatting? — S Meaden, May 27 '16 at 21:58
Unfortunately not. I think my code is much cleaner and I'd rather continue to deal with table objects as opposed to ranges. — noobmaster69, May 27 '16 at 22:01
Can't you paste-special the formats from a source row into that new target row? Generalized example: `Sheets(1).Range("A1:A200").Copy` then `Sheets(2).Range("B1").PasteSpecial xlPasteFormats` — Marc, May 27 '16 at 22:01
As Marc says. As a tip, if you don't know how to do something in VBA / Excel, just go to Record a new Macro, do the thing manually that you are trying to achieve, stop Recording, and then do an ALT & F11, go into the Module in the VBA editor and see what code it generated. As a second tip, you could streamline your code by not using public variables and, changing your Subs into Functions that pass in local variables and return the results that you then want to manipulate further. — Davy C, May 27 '16 at 23:51

score 0 · Answer 1 · answered May 28 '16 at 04:16

As a note you can return the startRow from Sheets(shtName).ListObjects(tblName).Range.Row within your Sub and should not need to be passed as attribute.

Also I wonder what format do you need to copy, when the default behavior of Excel when you insert a row or cells to copy the format of the cells above.

In any case, these added lines will do it (added Dims and the last few lines):

Private Sub addRow(shtName As String, tblName As String, startRow As Integer)

    Dim tableRef As Integer
    Dim addedCells
    Dim addedCellRange As Range
    Dim previousCellRange As Range

    Call getSelectedCell
    Call totalRowsInTable(shtName, tblName, startRow) ' Sets the selectedRow and tblTotalRows property

    ' We determine the row number where the new table row should be placed
    tableRef = selectedRow - startRow + 1

    ' Check to make sure the user is in the active table and then add a row
    If tableRef > 0 And selectedRow <= tblTotalRows Then
        Set addedCells = Sheets(shtName).ListObjects(tblName).ListRows.Add(tableRef)
    Else
    ' If they're not in the table and then add a row to the bottom of the table
        Set addedCells = Sheets(shtName).ListObjects(tblName).ListRows.Add 'AlwaysInsert:=True
    End If
    ' Copy formats here
    Set addedCellRange = addedCells.Range
    Set previousCellRange = Range(Cells(addedCellRange.Row - 1, addedCellRange.Column), Cells(addedCellRange.Row - 1, addedCellRange.Column + addedCellRange.Columns.Count - 1))
    previousCellRange.Copy
    addedCellRange.PasteSpecial xlPasteFormats
    Application.CutCopyMode = 0

End Sub

user3598756 · Accepted Answer · 2016-05-28T10:50:28.960

0

It can be shortened down to

Private Sub addRow(shtName As String, tblName As String)
    Dim newRow as Range

    With Sheets(shtName).ListObjects(tblName)
       If Not Intersect(ActiveCell,.Range) Is Nothing Then
            Set newRow= .ListRows.Add(ActiveCell.Row - .Range.Rows(1).Row + 1).Range
       Else
            Set newRow= .ListRows.Add(AlwaysInsert:=True).Range
       End If
    End With 

    With newRow
        .offset (-1).Copy
        .PasteSpecial xlPasteFormats
    End With 
    Application.CutCopyMode = False
End Sub

edited May 28 '16 at 10:50

answered May 28 '16 at 07:19

user3598756

28,893
4
18
28

@BarryBlade: did you get through it? – user3598756 Jun 01 '16 at 20:36

VBA Excel - Insert table row with same formatting as row above it

2 Answers2