CODEWITHSUNDEEP
excelvba
isinkzat
isinkzat

Reputation: 3

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

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.

Upvotes: 0

Views: 2279

Answers (1)

Aster Hu
Aster Hu

Reputation: 66

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

Upvotes: 1

Related Questions