PasteSpecial method of Range class failed when trying to paste into an added ListRow

Question

I want to copy/paste w transpose data A1:A61 from the active worksheet into columns 2 "B" through 62 "BJ" of the "Client Info" worksheet which contains a formatted table. I search for client name & phone in column 68 "BP" of the "ClientInfoTable". If found, that's the row I want to update, otherwise add a new row and paste into it. The PasteSpecial works fine when updating a FOUND record, but FAILS ONLY when trying to PasteSpecial into an added row. Error: PasteSpecial method of Range class failed.

Sub SaveToMaster(clientfirstname as String, clientlastname as String, clientphone as String)

Dim addedrow As ListRow
Dim tbl As ListObject
Dim clientnamephone As String
Dim foundcell As Range
Dim updaterow As Long

clientnamephone = clientfirstname & " " & clientlastname & " " & clientphone

Range("A1:A61").Copy
Worksheets("Client Info").Activate

Set tbl = ActiveSheet.ListObjects("ClientInfoTable")

Set foundcell = tbl.DataBodyRange.Columns(68).Find(What:=clientnamephone, LookIn:=xlValues, LookAt:=xlWhole)

If foundcell Is Nothing Then
    Set addedrow = tbl.ListRows.ADD
    updaterow = addedrow.Index
Else
    updaterow = tbl.ListRows(foundcell.Row - tbl.HeaderRowRange.Row).Index
End If

tbl.DataBodyRange(updaterow, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

Alicia Stevenson Alicia Stevenson · Accepted Answer · 2020-10-28T17:34:25

I figured out why: when you add a ListRow, it apparently clears the copied range. I corrected it by going back to the source workbook (after adding the listrow) and copying the source range.

PasteSpecial method of Range class failed when trying to paste into an added ListRow

1 Answers