Copy and Paste Data right under existing data

Question

My task is to copy individual table data in different sheets and paste all of them into a Master Table. I wrote the below coding but there is an error "Object Required" of my second last line.

I am specifically confused about how to paste data RIGHT UNDER the existing data. All of the separate sheets have same table formatting.

Any help is appreciated, thanks!

Private Sub CommandButton1_Click()

'Delete Master Table
Application.ScreenUpdating = False
Sheets("Pivot Table Data").ListObjects("MasterTable").Range.Select
Rows("4:" & Rows.Count).ClearContents   

'Copy and Paste data from other Sheets
Worksheets("HK-SLHK").ListObjects("TableHK").DataBodyRange.Copy Destination:=Worksheets("Pivot Table Data").Range("B4")
Application.CutCopyMode = False
Worksheets("Indonesia-SLFI").ListObjects("TableIndoSLFI").DataBodyRange.Copy Destination:=Worksheets("Pivot Table Data").Cells(Row.Count, B).End(xlUp).Row + 1
Application.CutCopyMode = False
End Sub

score 0 · Answer 1 · answered Feb 13 '18 at 09:55

In your code instead of Row.Count declare a variable for the last rows:

Dim myLastRow as Long

Then assign this variable to a function like this:

myLastRow = LastRow(Worksheets("Pivot Table Data"))
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

And use it in the destination:

Worksheets("Indonesia-SLFI").ListObjects("TableIndoSLFI").DataBodyRange.Copy _
      Destination:=Worksheets("Pivot Table Data").Cells(myLastRow, B).End(xlUp).Row + 1

Additionally:

Hi Vityata, thanks for your reply. However, I am still getting Object-defined error after implementing your answer. Do you have any thoughts? — Mklccs, Feb 21 '18 at 09:18
@Mklccs - where are you getting the error? Probably you are not setting the object correctly or the object does not exist. — Vityata, Feb 21 '18 at 09:28

Copy and Paste Data right under existing data

1 Answers1