Hi,
I'm making a workbook that I'm using as a job list for the company. As jobs are completed, I want the job (for each job, all the data is in contained in a single table row) to move to a separate table, in a separate worksheet within the same workbook. What I want to make sure of is this: that for each column of data in the table row getting moved/transferred from table #1 to the table #2, goes into the same (matching) column name as table #1. If no match is found, popup a msgbox, and don't do any transferring (exit sub). This would allow the two tables to have different column orders and still ensure the data transferred corresponds to the correct column name. Once the data is moved/transferred to the second table, the row in table 1 gets deleted/cleared. The data transfer is triggered by a worksheet event, if a cell in a column in table 1 called "Row Action" is "Move to completed" then... See below info. Thank you for any time looking at this for me!
Workbook= Priority List
Worksheet1=Job list
Table1=Job_List_Table
Worksheet2=Completed Job List
Table2=Completed_Job_List_Table
Here is code that is almost working, but I can't figure out how to get this line working, it says it is out of range.
.Columns(T2.ListColumns(strCurrentColName).Index).Value = c.Value
It is not recognizing the strCurrentColName as a valid variable? When i hover over it i see what is wrong, it needs to read the strCurrentColName with quotations, but it is not.
It is reading it as .Columns(T2.ListColumns(strCurrentColName).Index).Value and it needs to be .Columns(T2.ListColumns("strCurrentColName").Index).Value correct?
I'm open to better ways of doing this also, this is just what i came up with so far. Another note, with my current code the code to exit sub if no matching column is found prior to any data transfer isn't coded properly, but that is a sample of how i would want the code to function.
I'm making a workbook that I'm using as a job list for the company. As jobs are completed, I want the job (for each job, all the data is in contained in a single table row) to move to a separate table, in a separate worksheet within the same workbook. What I want to make sure of is this: that for each column of data in the table row getting moved/transferred from table #1 to the table #2, goes into the same (matching) column name as table #1. If no match is found, popup a msgbox, and don't do any transferring (exit sub). This would allow the two tables to have different column orders and still ensure the data transferred corresponds to the correct column name. Once the data is moved/transferred to the second table, the row in table 1 gets deleted/cleared. The data transfer is triggered by a worksheet event, if a cell in a column in table 1 called "Row Action" is "Move to completed" then... See below info. Thank you for any time looking at this for me!
Workbook= Priority List
Worksheet1=Job list
Table1=Job_List_Table
Worksheet2=Completed Job List
Table2=Completed_Job_List_Table
Here is code that is almost working, but I can't figure out how to get this line working, it says it is out of range.
.Columns(T2.ListColumns(strCurrentColName).Index).Value = c.Value
It is not recognizing the strCurrentColName as a valid variable? When i hover over it i see what is wrong, it needs to read the strCurrentColName with quotations, but it is not.
It is reading it as .Columns(T2.ListColumns(strCurrentColName).Index).Value and it needs to be .Columns(T2.ListColumns("strCurrentColName").Index).Value correct?
I'm open to better ways of doing this also, this is just what i came up with so far. Another note, with my current code the code to exit sub if no matching column is found prior to any data transfer isn't coded properly, but that is a sample of how i would want the code to function.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, ActiveSheet.ListObjects("Job_List_Table").ListColumns("Row Action").Range) Is Nothing Then
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim T1 As ListObject
Dim T2 As ListObject
Dim tblRow1 As Range
Dim tblRow2 As Range
Dim strCurrentColName As String
Set ws1 = ThisWorkbook.Sheets("Job List")
Set ws2 = ThisWorkbook.Sheets("Completed Job List")
Set T1 = ws1.ListObjects("Job_List_Table")
Set T2 = ws2.ListObjects("Completed_Job_List_Table")
If Target.Value = "Move to completed" Then
'If (no matching column found in Table 2,msgbox and exit the macro prior to any data transfer) Then
' MsgBox "There is not a matching column for " & strCurrentColName & ", this row will not be moved."
' Exit Sub
'End If
Set tblRow1 = Intersect(Target.EntireRow, ws1.ListObjects("Job_List_Table").Range)
Set tblRow2 = T2.ListRows.Add.Range
With tblRow2
For Each c In tblRow1
strCurrentColName = Cells(c.ListObject.Range.Row, c.Column).Value
.Columns(T2.ListColumns(strCurrentColName).Index).Value = c.Value 'NOT WORKING
Next c
End With
End If
End If
