I want to add a row only on the specified table and not on the whole sheet.
I want flexible code so I can use it in different templates; meaning:
Sub AddRowToTable()
Dim tbl As ListObject 'Needed to reference a table '
Dim selectedCell As Range
On Error Resume Next
'Temporarily ignore the error that might occur during exec.'
'e.g., user clicks Cancel / ESC'
Set selectedCell = Application.InputBox("Select a cell in the table below:", Type:=8)
'Prompt the user to select a cell'
On Error GoTo 0
Set tbl = selectedCell.Worksheet.ListObjects(1) 'Assumes that there is only one table in the active sheet'
If Not tbl Is Nothing And Not selectedCell Is Nothing Then 'Check if the selection is within the table'
If Not Intersect(selectedCell, tbl.DataBodyRange) Is Nothing Then
selectedCell.Offset(1, 0).EntireRow.Insert
'Insert a row below the selected cell within the table'
Else
MsgBox "Please select a cell within the table below."
End If
Else
MsgBox "No table found in the active sheet. Contact template owner."
End If
End Sub
Please try
Application.Intersect(selectedCell.EntireRow, tbl.Range).Insert
OR
tbl.ListRows.Add selectedCell.Row - tbl.DataBodyRange.Row + 1
Microsoft documentation: