Macro for vlookup b/w two workbooks

[ukarthik10]

Hello Everyone!!

I'm amateur in creating macros however, I wanna create a macro which looksup the value from source sheet to target sheet.

Can someone pls help!!

 

[Luke M]

Hello, and welcome to the forum.
Without some context as to the layout of your sheets, it will be very hard for us to even figure out what needs to be done in your sheets.
Also, I'm not sure why you would want to do this via macros. The faster way would be to just write a regular VLOOKUP formula. Now, you could have the macro write the formulas for you I suppose...but in that case, you can just record a macro of yourself setting up the formulas.

 

[Luke M]

Well, now I just feel foolish, because I just now saw that there already was some code written. Doh!

Looking over your code, I think this last line is missing the leading period to tie it back to the with statement. Change from this:

  Range("G2:G" & OutputLastRow).Formula = "=VLOOKUP(D2,[testbook.xlsm]pl!$D$2:$E$,2,0)"
                               .Value = .Value

to this:

.Range("G2:G" & OutputLastRow).Formula = "=VLOOKUP(D2,[testbook.xlsm]pl!$D$2:$E$,2,0)"
                               .Value = .Value

 

[Manuel_1983]

Hi Luke, I trying to do the same macro with other parameters, but I am getting lost with error messages. I have two files (Report and Data). in Data there are some data that I need to vlookup vs the file called Report. the column B is the link between the two spreadsheets.

I need the data for the columns F, G and H. If you help me with the column F, I will try to do the rest with the column G and H. Many thanks in advance.

Please see attached the 2 files.

looking forward your response!!! many thanks again

 

[Luke M]

Hi Manuel,

With both workbooks open, in Report workbook cell F2, put this formula:
=VLOOKUP($B2,'[DATA.xlsx]Master Report'!$B$1:$H$118,COLUMN(F$1)-1,)

Copy to the right and down as needed. This is the easiest way to get the data. If you don't need/understand macros, I would recommend avoiding them. Much easier in this case to stick with just a formula.

 

[Manuel_1983]

Hi Manuel,

With both workbooks open, in Report workbook cell F2, put this formula:
=VLOOKUP($B2,'[DATA.xlsx]Master Report'!$B$1:$H$118,COLUMN(F$1)-1,)

Copy to the right and down as needed. This is the easiest way to get the data. If you don't need/understand macros, I would recommend avoiding them. Much easier in this case to stick with just a formula.

Hi Luke,
first of all, many thanks for the prompt response. Unfortunately, this I have to do it through a macro. The files sent are a simplified version of what the macro does. (twice a week, I have to generate a report and format it in some sort of way, plus get some information doing a vlookup).

Because i cant send you the whole file, I simplified the two files with some random text.
if you help with the way to use vlookup in vba, I can use it for many other colleagues using this macro as a template for others.

Could you please help me? I will be very appreciate.
Kind regards,
Manuel

 

[Manuel_1983]

Hi again,

I did it.
I use this code:

'
With Range("F2")
.Formula = "=VLookup(RC[-4],'[Master Report - Template.xlsx]Master Report'!C2:C6,5,0)"

'this makes that leave in blanc empty cells.
With .Resize(Range("k" & Rows.Count).End(xlUp).Row - 1)
.FillDown
.Copy
.PasteSpecial xlPasteValues
End With

End With


Many thanks for your response before. it triggered further my investigation and then I got the solution to my problem.
Best regards,
Manuel