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.
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
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