Help with existing Vlookup macro

[mporres77]

Hello.

I am wondering if anyone can help me with this macro. It is assigned to a button.

I am trying to have the macro look for whatever number is in cell A4 Sheet 1, then go to Sheet 2, look for that same number in column A and paste the data from Sheet 1 Range A4:G4 where ever it found the number in Sheet 2.

The problem I am having is that right now my macro is pasting Sheet 1 Range A4:G4 in Sheet 2 in the last row that has data.

I can't seem to figure out how to get it to find the number in Sheet 2 and past the data in that row.


Here is what I have so far:
Sub VlookupA()

Application.ScreenUpdating = False
Dim j As Long
Dim SN As Long
Set myrange = Range("A:A")

j = Sheet5.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
SN = Application.WorksheetFunction.VLookup(SN, myrange, 1, True)
On Error GoTo 0
Sheet1.Range("A4:G4").Copy
Sheet5.Range("A" & j).PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = False
End Sub

Thank you to anyone in advance for you help.

 

[NARAYANK991]

Hi ,

See if this is correct.

Narayan

 

[mporres77]

Hi ,

See if this is correct.

Narayan

Wow brilliant. Thank you so much. I am going to look at your code and learn what it is doing. A million thank yous.