CODEWITHSUNDEEP
excelvba
Nick
Nick

Reputation: 687

How to Copy Data from range to last row in table (transposed)?

Hello everyone I'm back for my second beginner VBA question (Same project as before)

Information: 2 Sheets - Database & Entry

Both contain the same two tables just that Entry is transposed (headers in rows instead of columns)

My goal is to copy the data from entry into the Databases' last row while adding an additional one. Ideally there would be a way to copy the formula's in data entry right over (or format the database columns to contain them)

The data is sorted and filtered in the database afterwards, hence the need for a table. I'm having real trouble with this entire ListObject thing and understanding how it works.

Returns run time error 1004: PasteSpecial method of range class failed on this line:

tbl.Range(LastRow).PasteSpecial Paste:=xlValues, Transpose:=True

However if I change that line to:
Sheets("Database").ListObjects("Entire").PasteSpecial Paste:=xlValues, Transpose:=True

i get a runtime 438 (object doesn't support this property or method) error, which i find very confusing cause they should be the same thing

Thank you for any help or suggestions, they are greatly appreciated! Also slightly off-topic but if you know any good resource I could buy to improve feel free to let me know. 

  Private Sub CommandButton2_Click()

Dim Entry As Worksheet
  Set Entry = ActiveSheet

Dim tbl As ListObject
  Set tbl = Sheets("Database").ListObjects("Entire")

Dim LastRow As Integer
  LastRow = tbl.Range.Rows.Count

Application.ScreenUpdating = False
    If Entry.Range("E9") = "y" Then
        Entry.Range("E6:E100").Copy
        tbl.ListRows.Add AlwaysInsert:=True
        tbl.Range(LastRow).PasteSpecial Paste:=xlValues, Transpose:=True

        Entry.Columns("E").Delete
    Else
        Entry.Columns("E").Delete
    End If
Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 1643

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

It's very easy to clear the clipboard in Excel accidentally, so you should (probably) always call .Copy immediately before .Paste/.PasteSpecial. Changing the order of

Entry.Range("E6:E100").Copy
tbl.ListRows.Add AlwaysInsert:=True

solves the problem.

Upvotes: 1

Related Questions