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