Hi Forum,

Once again I find myself turning to this great forum to try to find a solution to a VBA question I have. I'm wanting to insert a column onto a spreadsheet that contains a vLookup formula. Right now I have the following code, which copies select columns from one worksheet ("SiteMaster") and copies the columns to a second worksheet ("HSmSchedule"):

Sub CopyColumns()
     
    Application.Calculation = xlCalculationManual
    Sheets("SiteMaster").Columns("A:A").Copy Sheets("HSmSchedule").Columns("A:A")
    'Sheets("SiteMaster").Columns("B:B").Copy Sheets("HSmSchedule").Columns("B:B") <---- This is where I want to add the vLookup
    Sheets("SiteMaster").Columns("DF:DF").Copy Sheets("HSmSchedule").Columns("C:C")
    Sheets("SiteMaster").Columns("DG:DG").Copy Sheets("HSmSchedule").Columns("D:D")
    Sheets("SiteMaster").Columns("DH:DH").Copy Sheets("HSmSchedule").Columns("E:E")
    Sheets("SiteMaster").Columns("DI:DI").Copy Sheets("HSmSchedule").Columns("F:F")
    Sheets("SiteMaster").Columns("DJ:DJ").Copy Sheets("HSmSchedule").Columns("G:G")
    Sheets("SiteMaster").Columns("DK:DK").Copy Sheets("HSmSchedule").Columns("H:H")
    Sheets("SiteMaster").Columns("DL:DL").Copy Sheets("HSmSchedule").Columns("I:I")
    Sheets("SiteMaster").Columns("DM:DM").Copy Sheets("HSmSchedule").Columns("J:J")
    Sheets("SiteMaster").Columns("DN:DN").Copy Sheets("HSmSchedule").Columns("K:K")
    Sheets("SiteMaster").Columns("DO:DO").Copy Sheets("HSmSchedule").Columns("L:L")
    Sheets("SiteMaster").Columns("DP:DP").Copy Sheets("HSmSchedule").Columns("M:M")
    Sheets("SiteMaster").Columns("DQ:DQ").Copy Sheets("HSmSchedule").Columns("N:N")
    Sheets("SiteMaster").Columns("DR:DR").Copy Sheets("HSmSchedule").Columns("O:O")
    Sheets("SiteMaster").Columns("DS:DS").Copy Sheets("HSmSchedule").Columns("P:P")
    Sheets("SiteMaster").Columns("DT:DT").Copy Sheets("HSmSchedule").Columns("Q:Q")
    Sheets("SiteMaster").Columns("DU:DU").Copy Sheets("HSmSchedule").Columns("R:R")
    Sheets("SiteMaster").Columns("DV:DV").Copy Sheets("HSmSchedule").Columns("S:S")
    Sheets("SiteMaster").Columns("DW:DW").Copy Sheets("HSmSchedule").Columns("T:T")
    Sheets("SiteMaster").Columns("DX:DX").Copy Sheets("HSmSchedule").Columns("U:U")
    
 Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False

End Sub
I want to add the following vLookup 
=VLOOKUP(A2,Provider,2,TRUE)
into column B on the second worksheet ("HSmSchedule"), but I only want the formula to be copied to rows that contain values copied into column A on the second worksheet. Anybody have any ideas on how I can make this happen? Thanks!