moving data between excel tables in 2 different tabs (same workbook)

[SandeepExcel]

Hi,

I have an excel(attached) file with 2 Tabs (Data, FinalResults). "Data" tab has a Table (Table1) which will have some data. the "Data" tab also has a button, which when clicked copies all the the data from Table1 and pastes it into "Table2" on the second tab (FinalResults). and following is the code used for the button on click action.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub CommandButton1_Click()

Application.ScreenUpdating = True
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim LastRow As Long
Dim sh3Col As Integer

Set pasteSheet = Worksheets("FinalResults")

Set copySheet = Worksheets("Data")
copySheet.Range("table1").Copy Destination:=pasteSheet.Range("table2").Cells(1)

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub</code>

The problem is that the action is overwriting some of the existing text below Table2 in the FinalResults tab. I need help with modifying the above code to avoid the overwrite of the existing cells below the Table2. instead it should simply shift the cells below while pasting the data (the Tab "DesiredResults" is how I would like the output to be). just for information purposes I have added another tab "FinalResults_BeforCopy" so that you can see how the tab "FinalResults" looks before the button is clicked.

any help is highly appreciated.

following is the link to a sample Excel

https://drive.google.com/file/d/1AY3Bq79es2EK-ZFHEQ52CdOkZaA-7rTX/view?usp=sharing

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
    Dim tbl1 As ListObject, tbl2 As ListObject
    
    Application.ScreenUpdating = False
    Set tbl1 = Sheets("Data").ListObjects("Table1")
    Set tbl2 = Sheets("FinalResults").ListObjects("Table2")
    With tbl2.DataBodyRange
        If .Rows.Count > 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        .Cells.ClearContents
        .Resize(tbl1.DataBodyRange.Rows.Count - 1).Rows.Insert shift:=xlDown
    End With
    tbl2.DataBodyRange.Value = tbl1.DataBodyRange.Value
    Application.ScreenUpdating = True


End Sub

 

[SandeepExcel]

Hello Dante,

That worked like a charm! thank you so much for your help and time.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.