VBA to Insert Column, Lookup to another workbook, return results.

[brlattim]

I have a process that multiple users need to run daily. I am looking to automate it for them.

Deliverables
The spreadsheet I am pulling data into (and user will have open is located here: G:\PHBIT_Review
File name is: Sample_Printable.xlsx
Tab name would be Printable_Version
The file I am looking to open (or pull data from) is located here: G:\PHBIT_Review
File name is: Printable_Instructions.xlsx.
Tab name would be Printable_Version

I am looking to do the following
1) Insert column on Sample_Printable (1st file above) into column C. There is already data in column C, so insert into C and assume other data shifts right.
2) In cell C2, add a vlookup to Printable_Instructions. The vlookup would be =VLOOKUP(A2,'[Printable_Instructions.xlsx]Printable_Version'!$A:$C,3,FALSE).

Ideally the formula would cascade down to the end of the dataset. Both data sets on either file might fluctuate # of rows so I think my formula accommodates for that.

 

[dermie_72]

If it's the same file names and the same formula each time, you could record a macro doing the exact steps you stated above. Then you could change the range to include a last row variable.

Dim LR as long
LR = Cells(Rows.count, "A").End(xlUp).Row

Then where the cell selection for C2 happens, change that to ("C2:C" & LR)
This will put the formula in the entire range the same way each time.