Macro to Compare two sheets, if match then copy two cells from one sheet to the other

[ali.whitaker]

I'm still new but I think this breakdown explains it best...

I need a Macro that accomplishes the following:

I need to compare Column "A" of worksheet "Master" (Workbook named same) against Column "X" of Worksheet "Report" (Workbook named same)
If a match is found, copy cells (in the same row the match was found in "Report") from Column "A" and "AS" of "Report"
Then paste those two copied cells into (the row where the match was found in "Master") Columns "AC" and "AF" of "Master"

Hopefully it's not too difficult... Many thanks in advance.

P.S. - I tried posting like this once before and a "SUPERHERO" by the name of StevenM managed to provide the most awesome Macro to solve my problem. I hope someone can do the same in this instance.

[tlafferty]

Please post a sample workbook and I'll take a swing at it. It would be nice if you could put explanatory comments in your sample book as well as what you'd like the output to look like. I have a pretty good grasp of what you're after but not too sure.

[StevenM]

Backup your data.
Copy the following code in a standard module.
Run: CopyAnAS2ACnAF

' I need to compare Column "A" of worksheet "Master" (Workbook named same)
' against Column "X" of Worksheet "Report" (Workbook named same)
' If a match is found,
' copy cells (in the same row the match was found in "Report") from Column "A" and "AS" of "Report"
' Then paste those two copied cells into (the row where the match was found in "Master") Columns "AC" and "AF" of "Master"
Sub CopyAnAS2ACnAF()
    Dim wsM As Worksheet
    Dim wsR As Worksheet
    Dim nRow As Long
    Dim nLastRow As Long
    Dim rgCell As Range
    
    Set wsM = Worksheets("Master")
    Set wsR = Worksheets("Report")
    
    With wsM
        nLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Assuming that row 1 is the header row
        For nRow = 2 To nLastRow
            Set rgCell = wsR.Range("X:X").Find(What:=.Cells(nRow, "A"), _
                           LookIn:=xlValues, _
                           Lookat:=xlWhole, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=True)
            If Not rgCell Is Nothing Then
                .Cells(nRow, "AC") = wsR.Cells(rgCell.Row, "A")
                .Cells(nRow, "AF") = wsR.Cells(rgCell.Row, "AS")
            End If
        Next nRow
    End With
End Sub

The code is very basic (and you should be able to fix anything if I didn't understand what you wanted).
(1) I go through Column A of worksheet "Master"
(2) I look for a match in column X of "Report"
(3) If match is found two items are copied.
The left side of equals is a cell in worksheet "Master"
The right side of equals is a cell in worksheet "Report"

In the future, when you make such a request, it helps if you have a mock workbook uploaded. It would help me to better understanding what you need, and then I can use that workbook to test the code I'm writing.

Also, you can change the name of the macro to something more meaningful.