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

SandeepExcel

New Member
Joined
Mar 14, 2019
Messages
2
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:
Code:
<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


 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Try this

Code:
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
 
Last edited:
Upvote 0
I'm glad to help you. I appreciate your kind comments.
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top