Thread: Macro: open file, read data, create table, vlookup

Is there a way in excel to do some semi-automated pattern matching and add a new column as a result? Im sure I can use VLOOKUP for the pattern matching, but how do I create a macro to automate the entire process of opening a existing file, create a vlookup table and inserting the correct vlookup syntax as a new column? Here are some specifics:

I have two excel files. One contains only two columns and essentially never changes (Masterlist). The second file is a report that is generated daily with different bits of data. However there is one common element between the two files called "SiteID." For each daily report I would like to be able to match the SiteID to the Masterlist and add the second column from the Masterlist to the daily report. Example:

Here is what I have now...

Masterlist:
SiteID, Name
01232, bob
03432, james
12243, silvia
23543, justin

DailyReport:
PI, SiteID, City, DOB
qw, 01232, Morrisville, 1980
sd, 12243, Cary, 1961
gw, 23543, Pittsburgh, 1978
as, 03432, Franklin, 1998

Here is what I would like...

DailyReport:
Name, PI, SiteID, City, DOB
bob, qw, 01232, Morrisville, 1980
silvia, sd, 12243, Cary, 1961
justin, gw, 23543, Pittsburgh, 1978
james, as, 03432, Franklin, 1998

This really is a multi-step process that requires a macro. The steps are this:
(1) Create a new column in "dailyreport"
(2) label the column
(3) determine which rows in the column need the lookup function (or at least where to end)
(4) insert the approprite lookup in the columns from #3

This works for #1 and #2

Sub CreateColumn()
    Columns(3).Insert

    Range("C4").Select
    ActiveCell.FormulaR1C1 = "P.I. Name"
    With ActiveCell.Characters(Start:=1, Length:=9).Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 4
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("C5").Select
    
End Sub

This works for #4:

=VLOOKUP(B5,'c:\[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)

The question becomes how to I perform #3 and what needs to be edited in the VLOOKUP for automatic pasting via a macro?
Thanks!