How to use VLOOKUP to copy range to another workbook?

U

UNISON Issy

I want to search for a match to an identifier in Column A of Sheet1. If
found, copy the whole of the associated row to Sheet2, where other columns
will be added after the copied data. If not found, return a "Not found"
message. There could be up to 12000 rows in main worksheet. Relatively
novice user so nothing too complicated, please!
 
T

Toppers

Hi,
A starter. It assumes your Identifier is TEXT

Sub GetMatch(ByVal MatchKey As String, ByVal MatchRng As Range)
res = Application.Match(MatchKey, MatchRng, 0)
If IsError(res) Then
MsgBox " No match found for " & MatchKey
Else
Worksheets("Sheet1").Cells(res, "A").EntireRow.Copy _
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp)(2)
End If
End Sub


Sub test()
Set rng = Worksheets("Sheet1").Range("A1:a12000") '<=== Change to suit but
start at A1
' Add a loop to go through your ID s
Call GetMatch("5", rng)
End Sub


HTH
 
Top