Run-time Error '1004' ActiveSheet.Paste into a table failing

Question

Hello I am new to VBA and stackoverflow.

my program simply selects Table1 and copies it to the end of Table2. The code is as follows.

Sub c_p()



    Application.Goto Reference:="Table1"
    Selection.Copy
    Application.Goto Reference:="Table2"
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    ActiveSheet.Paste


End Sub

When I run the program, it gives "Run-time Error '1004' ". The error in this line:

    ActiveSheet.Paste

I am aware that there are better ways to go about this than using selections and while those are interesting to me I would also like to know the reason for the problem.

Thank you for your help.

I don't have access to Excel at the moment and can think of a few things, try changing .Paste to .PasteSpecial xlPasteValues. Here is a great reference/guide on referencing tables. https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables — Captain Grumpy, Dec 16 '16 at 04:13
Don't you need to specify a range before pasting? What about `Activesheet.Selection.Paste`? (Also, it's best to [avoid using `.Activate`/`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Dec 16 '16 at 06:47
**Captain Grumpy** - Thanks fo the link. it really helped **A.S.H** - No — Jonathan Patao, Dec 16 '16 at 12:50

score 1 · Accepted Answer · edited Dec 16 '16 at 06:32

Your issue appears to be with "Selection.ListObject.ListRows.Add AlwaysInsert:=False" after you have copied the data. It seems to cause the system to "forget" the copied data.

This might be an adequate solution

Sub c_p()
    Application.Goto Reference:="Table1"
    Selection.Copy
    Application.Goto Reference:="Table2"
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Application.Goto Reference:="Table2"
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    ActiveSheet.Paste
End Sub

score 1 · Answer 2 · answered Dec 16 '16 at 12:47

thank you alot

you were right that the system forgot the copied data..

about the code - your changes didn't help with the forgotten information but with some amendments it works great.

here is the final code:

Sub c_p2()

    Application.Goto Reference:="Table2"
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    Application.Goto Reference:="Table1"
    Selection.Copy
    Application.Goto Reference:="Table2"
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    ActiveSheet.Paste
End Sub

Run-time Error '1004' ActiveSheet.Paste into a table failing

2 Answers2