VBA Macro for Pasting Data In New Row of Table - Excel

Question

I have recorded a macro that is attempting to copy information from cells outside of a table and paste them into a new row in a table on the same sheet. When trying to run the macro I receive "Run-time error '1004': PasteSpecial method of Range class failed." The issue seems to be with the first line stating:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

I have a collection of paste special code in this module so I am afraid that this first line might not be the only issue. Below is the code I have so far.

Sub PlaceOrder()
'
' PlaceOrder Macro
'

'
    Range("A3").Select
    Selection.Copy
    Range("Table1[[#Headers],[Balance]]").Select
    Selection.End(xlDown).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B23").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range("C3:E3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 3).Range("A1").Select
    Range("F3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 3).Range("A1").Select
    Range("E3").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D3").Select
    Selection.ClearContents
    Range("C3").Select
    Selection.ClearContents
    Range("B3").Select
    Selection.ClearContents
    Selection.ConvertToLinkedDataType ServiceID:=268435456, LanguageCulture:= _
        "en-US"
End Sub

Any help will is greatly appreciated!

Edit: Worksheet

Attached is screenshot of the worksheet I am working with. I would like to be able to paste the values of A3 & C3-F3, and the formula in B3 into the table seen below. A new row needs to be inserted prior to pasting all of this information.

Could you share the screenshots of the initial and the resulting (desired) worksheets? — VBasic2008
Thank you for replying to my question. I added a screenshot of my worksheet and gave a little more background into what I'm trying to accomplish. — isinkzat

Aster Hu Aster Hu · Accepted Answer · 2021-06-21T20:27:06

This should work. It's basically just a clearer version of your code.

Sub PlaceOrder()

Dim tbl As ListObject
Dim LastRow As Long
Set tbl = ActiveSheet.ListObjects("Table1")
LastRow = tbl.Range.Rows.Count    'get # of last row

With ActiveSheet
    'copy and paste A3
    .Range("A3").Copy
    tbl.Range(LastRow, 1).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    'copy and paste B3
    .Range("B3").Copy
    tbl.Range(LastRow, 2).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
    
    'copy and paste C3:F3
    .Range("C3:F3").Copy
    tbl.Range(LastRow, 3).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    'clear value in B3:F3
    .Range("B3:F3").ClearContents
    
End With

End Sub

Your original macro did not work because the system forgot the copied value after this line: Selection.ListObject.ListRows.Add AlwaysInsert:=False

VBA Macro for Pasting Data In New Row of Table - Excel

1 Answers