# Copying info from one table to another table



## Boua (Dec 28, 2022)

I hope that someone can help me with the following problem. I have a workbook with different sheets and tables. A commandbutton copies a ListedRow to another table. I created a small workbook that explains my problem.
The code that copies the full ListedRow works perfect. Based upon a "yes" in the last column the row is copied to the other table.  But i don't need the full row, but only the first 8 columns. Thus  Name, FirstName, FullName, State, Address, Zip, City and Info1. So if i select a "yes" from the dropdown in the last column and i click on the commandbutton, the corresponding 8 columns must be copied to the other table and then the full row has to be deleted. This have to work in both directions. From table1 to table2 and from table2 to table1. 

The code for the commandbutton
Option Explicit

Sub CopyStudentInfoToOtherTable()

'Declaration
   Dim tblShRead As Object              'table that's been read
   Dim tblShWrite As Object             'table where info will be written too
   Dim RowsInTable As Long, x As Long   'last row in a table, x used as counter
   Dim srcRow As Range                  'row in table that's been read
   Dim dstRow As Object                 'row that will be written

 'assignments
 'checks which sheet is active
 'source and destination will be adapted
    If ActiveSheet.CodeName = "Blad1" Then
      Set tblShRead = Blad1.ListObjects("table1")
      Set tblShWrite = Blad2.ListObjects("table2")
    Else
      Set tblShRead = Blad2.ListObjects("table2")
      Set tblShWrite = Blad1.ListObjects("table1")
    End If

   RowsInTable = tblShRead.ListRows.Count

   For x = RowsInTable To 1 Step -1
   'copy of fullrow from sourcetable to estinationtable if column 8 in sourcetable is yes
   'The copy in the destinationtable is on the first line of the Databody

      If tblShRead.DataBodyRange.Cells(x, 10) = "yes" Then
         Set srcRow = tblShRead.ListRows(x).Range
         Set dstRow = tblShWrite.ListRows.Add(1)  'insert empty line in destinationtable
         srcRow.Copy
         dstRow.Range(1, 1).PasteSpecial xlPasteValues
         srcRow.Delete   'delete fullrow from sourcetable
      End If
   Next x

End Sub


----------



## Alex Blakenburg (Dec 29, 2022)

Just use resize the your copy line.

```
srcRow.Resize(, 8).Copy
```


----------



## Boua (Dec 29, 2022)

You made my day. It works perfect.
Thanks.

Can i ask an extra small question?
Suppose i want to copy only the first 2 columns and then columns 5,6 and 7. Based upon the same condition ('yes" in column 8)

Thx


----------



## Alex Blakenburg (Dec 29, 2022)

Boua said:


> Can i ask an extra small question?
> Suppose i want to copy only the first 2 columns and then columns 5,6 and 7. Based upon the same condition ('yes" in column 8)


The quickest change would be:


```
'Replace this:-
'srcRow.Resize(, 8).Copy
'dstRow.Range(1, 1).PasteSpecial xlPasteValues

'With this
srcRow.Resize(, 2).Copy
dstRow.Range(1, 1).PasteSpecial xlPasteValues

srcRow.Resize(, 3).Offset(, 4).Copy
dstRow.Range(1, 1).Offset(, 4).PasteSpecial xlPasteValues
```


----------



## Boua (Dec 30, 2022)

thank you for all the info.
Very much appreciated.


----------



## Alex Blakenburg (Dec 30, 2022)

You're welcome. Glad I could help.


----------

