Search code examples
excelvbacopycell

Insert tablerow and paste formula in it

I have a protected Excel sheet with a table in it.
Users can add data to the table and insert and delete rows.

I have working VBA to insert (and delete) a row, but it won't copy the formula from the cell above (this case J24).

Screenshot:
screenshot
(source: mobadvies.nl)

The VBA is:

Private Sub CommandButton1_Click() 
    ActiveSheet.Unprotect Password:="password"
    ActiveSheet.ListObjects("Tabel2").ListRows.Add AlwaysInsert:=True
    ActiveSheet.Protect Password:="password" 
End Sub

Private Sub CommandButton2_Click() 
    ActiveSheet.Unprotect Password:="password"
    ActiveSheet.ListObjects("Tabel2").ListRows(ActiveSheet.ListObjects("Tabel2").ListRows.Count).Delete
    ActiveSheet.Protect Password:="password" 
End Sub
Solution

  • Try something like this?

    Private Sub CommandButton21_Click()
ActiveSheet.Unprotect Password:="password"
Dim NewRow As ListRow
Set NewRow = ActiveSheet.ListObjects("Table1").ListRows.Add(AlwaysInsert:=True)
ActiveSheet.ListObjects("Table1").ListRows(1).Range.Select
Selection.Copy
NewRow.Range.Cells(1, 1).Select
ActiveSheet.Paste
ActiveSheet.Protect Password:="password"
End Sub

Private Sub CommandButton22_Click()
ActiveSheet.Unprotect Password:="password"
ActiveSheet.ListObjects("Table1").ListRows(ActiveSheet.ListObjects("Table1").ListRows.Count).Delete
ActiveSheet.Protect Password:="password"
End Sub

    Essentially it copys the formula from the first row into the newly created row. I'm new to VBA so not sure if this is the best way to do this or what you're looking for. Hope this helps!

    EDIT:

    Private Sub CommandButton21_Click()
ActiveSheet.Unprotect Password:="password"
Dim NewRow As ListRow
Set NewRow = ActiveSheet.ListObjects("Table2").ListRows.Add(AlwaysInsert:=True)
ActiveSheet.ListObjects("Table2").ListRows(1).Range.Cells(10).Select
Selection.Copy
NewRow.Range.Cells(10).Select
ActiveSheet.Paste
ActiveSheet.Protect Password:="password"
End Sub

    Try it like this then. This will copy the first row, cell 10 (J 23 in your case) and will copy it down to the newest row cell 10 (J24, J25 etc...) Let me know if it works!