excelvba

VBA code to copy some of the row cells based on match at column B


My question might be common but unfortunately, I didn't find a code while searching to apply my needs exactly.

I have a table with huge data for agents' new deals and history. For sure some of the agents' names are repeated many times and some of their basic information stays the same every time. I need code to copy and paste automatically the basic information of the agent from some columns to the new row if I enter a matching name in column B As you can see in my test image below C4& C8 when I enter a name that is found before in column "b" I need to copy and paste columns "C,E,G,I,J,L,M,N" automatically to the new matching name row.

any suggestions, please.

enter image description here


Solution

  • One way would be to use the Worksheet_Change method. Something like this should work:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim KeyCells As Range
    
        Set KeyCells = Range("B1:B1000")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        
            Dim lastCell As Range
            With ThisWorkbook.Sheets("Sheet1").Columns(2)
                Set lastCell = .Find("*", .Cells(1), xlFormulas, , xlByColumns, xlPrevious)
            End With
            If lastCell.Address = Target.Address Then
                Dim i As Integer
                Dim t As Integer
                t = lastCell.Cells.Row
                For i = 2 To t - 1
                    If Cells(i, 2) = lastCell Then
                        Cells(t, 3) = Cells(i, 3)
                        Cells(t, 5) = Cells(i, 5)
                        Exit For
                    End If
                Next
                
            End If
        End If
    
    End Sub
    

    By way of explanation. Worksheet_Change is triggered by a change to any cell in the Worksheet, but we are only interested in one column, so we first check to see if there is any overlap between the cell changing and the target column. (Note I have limited it to the first 1,000 rows of B).

    I have also assumed that you really only want to respond to a change in a new cell added at the end. Hence I check that the changing cell has the same address as the last cell in B.

    If it does, I then loop through all preceding values in B to test if the new value already occurred. If so, I copy the values. (My code only copies two, but it is trivial to extend).

    Notice also that I break on finding a previous value. If there can be multiple previous entries with different values, you may want to do something different. One idea would be to run the for loop backwards, to get the last matching value, not the first.