Copy and Paste VBA Code

[Grunt]

Hi. I'm using Excel 2003 and having no sucess in writing a VBA code for the below. Appreciate any help I can get:


Look at the invoice numbers in column B of sheet X.

If any match the invoice numbers in column A of sheet Y

Copy the date in column V of sheet X of the match

And paste to the match in column D of sheet Y.


In this doesn't make sense let me know.

 

[Glory]

Public Sub test2()
 
    a = Application.Match(ActiveCell, Sheets("SheetY").Range("A:A"))
    b = Sheets("SheetX").Range(ActiveCell.Address).Offset(0, 20)
 
    If Not IsError(a) Then
        Sheets("SheetY").Range("D" & a) = _
            Sheets("SheetX").Range(ActiveCell.Address).Offset(0, 20)
 
    Else
        MsgBox "No matching invoice number found on ""SheetY""!"
    End If
 
End Sub

Run this with your cursor on the cell containing the invoice number you want to work with.

 

[Grunt]

Glory, many thanks for the response. I asserted the code, but cannot get it to work. I originally created a module and pasted the code there. I then went back to sheet X and made a macro and macro button called "run macro" and linked the button to the module. When I press the button I receive a "run-time error 9 subscript out of range" message. I believe the problem may relate to where I have the code. If correct please assist. Thanks- Grunt