copy cell data from one sheet to another depending

Question

I saw the similar (almost same question) in the forum. Here is the chosen answer.

code:

Sub fixThis()
    Dim i As Long, j As Long, col1 As Long, col2 As Long, lastrow1 As Long, lastrow2 As Long
    Dim sheetOne As String
    Dim sheetTwo As String

    col1 = 5
    col2 = 1
    sheetOne = "Names"
    sheetTwo = "Job"
    lastrow1 = Sheets(sheetOne).Cells(Sheets(sheetOne).Rows.Count, col1).End(xlUp).Row
    lastrow2 = Sheets(sheetTwo).Cells(Sheets(sheetTwo).Rows.Count, col2).End(xlUp).Row

    For i = 2 To lastrow1
        For j = 2 To lastrow2
            If Sheets(sheetOne).Cells(i, col1).Value = Sheets(sheetTwo).Cells(j, col2).Value Then
                Sheets(sheetOne).Cells(i, 6).Value = Sheets(sheetTwo).Cells(j, 2).Value
            End If
        Next j
    Next i
End Sub

But I dont know how to turn it into answer for my case.

I have 2 sheets with name add and remove. I want to compare first column of both sheets. If a value matches in both sheets, I want to copy the value of status column from sheet "add" to sheet "remove" for the matched value.

Please help me with this.

Ps: I am very beginner at coding.

Answer 1

Sub fixThis()
    Dim i As Long, j As Long, colStatus As Long, lastrowAdd As Long, lastrowRemove As Long

    colStatus = 2 'your status column number
    lastrowAdd = Sheets("Add").Cells(Sheets("Add").Rows.Count, 1).End(xlUp).Row
    lastrowRemove = Sheets("Remove").Cells(Sheets("Remove").Rows.Count, 1).End(xlUp).Row

    For i = 1 To lastrowAdd 
        For j = 1 To lastrowRemove 
            If Sheets("Add").Cells(i, 1).Value = Sheets("Remove").Cells(j, 1).Value Then
                Sheets("Remove").Cells(j, colStatus).Value = Sheets("Add").Cells(i, colStatus).Value
            End If
        Next j
    Next i
End Sub

Answer 2

No need for VBA. This can be achieved using Vlookup. Please refer to below image.