Macro: Search for text from a cell in another sheet and copy it in

I have a spreadsheet with two tabs.

In tab one is a column with file names. I would like to apply a macro which searches the column of information in Sheet 1 for terms from a range of data in the Sheet 2. If it finds the term, it then copies the data from Sheet 2 into Sheet 1.


E.g.

Sheet 1
A B C
MTC_CBM_146_16483.pdf

MTC_CBM_146_3177.pdf

MTC_CCO_146_2564.pdf

MTC_CCO_146_2565.pdf

MTC_CCO_146_2566.pdf

MTC_CCO_146_2567.pdf

MTC_CCO_146_3606.pdf

MTC_CCO_146_3607.pdf

MTC_CCO_146_3745.pdf

MTC_CCO_146_3746.pdf

MTC_CCO_146_3748.pdf

MTC_CCO_146_3751.pdf

MTC_DCP_146_15848.pdf

MTC_DCP_146_17314.pdf

MTC_DCP_146_1798.pdf

MTC_DCP_146_2192.pdf

MTC_DCP_146_2453.pdf

MTC_DCP_146_2457.pdf

MTC_DCP_146_2458.pdf

MTC_DCP_146_2460.pdf

MTC_DCP_146_2467.pdf

MTC_DCP_146_2468.pdf



Sheet 2


A B
CBM Composition of a Sample of Bituminous Material
CBR Equivalent California Bearing Ratio
CCO Concrete Core Logs
CM_ Constituent Material in Recycled Aggregate
CMS Cover Meter Surveys
COC Certificate of Conformity
CPR Concrete Pour
CUB Concrete Cubes
DCP Dynamic Cone Penetration



So the macro looks at the list in sheet 1 and will then look at the data in sheet 2 bringing in the appropriate data from columns A and B so that the table once completed would look like:


A B C
MTC_CBM_146_16483.pdf CBM Composition of a Sample of Bituminous Material
MTC_CBM_146_3177.pdf CBM Composition of a Sample of Bituminous Material
MTC_CCO_146_2564.pdf CCO Concrete Core Logs
MTC_CCO_146_2565.pdf CCO Concrete Core Logs
MTC_DCP_146_15848.pdf DCP Dynamic Cone Penetration
MTC_DCP_146_17314.pdf DCP Dynamic Cone Penetration
MTC_DCP_146_1798.pdf DCP Dynamic Cone Penetration


I had started with the following code but don’t know how to carry out the functions I need!

Sub FindRefterms()

vStartRow = 2

For i = vStartRow To Cells(Rows.Count, "A").End(xlUp).Row

If InStr(1, Range("A" & i).Value, "Sheet 2 Text in Cell A2 to A67") Then
Range("B" & i).Value = "Text in Sheet 2 Cell A2 for e.g"
Range("C" & i).Value = "Text in Sheet 2 Cell B2 for e.g"


End If
Next i

End Sub

Thank you for any help!