Link to home
Create AccountLog in
Avatar of Justincut
Justincut

asked on

How to do a Vlookup from 1 spreadsheet to another using VBA code.

HI Guys, I have 2 Excel attachments , 1 called "Daily GSFI" and 1 called "WTD Commentary". From the WTD Commentary spreadsheet, I want to "View -> Windows -> Activate "Daily GSFI"Spreadsheet -> Create a range from "A3:I36" called "Region" (with which to do a Vlookup on later) -> View -> Windows -> Open "WTD Commentary" -> put a formula in F45 to F56 -> "=Vlookup(B45, Region, 9, False). How do I do this?
The problem is the "Daily GSFI" spreadsheet has a different ending in the name every day, eg, "Daily GSFI 18112014.xlsm" so it needs to defined in a variable.
If the Filename was not variable, the formula would look like this:
=VLOOKUP(B45,'[DailyGSFI.xlsm]Daily GSFI PL'!$A$3:$I$37,9,FALSE)
WTDCommentary.xlsm
DailyGSFI.xlsm
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Hello

Looking at your request and looking at the files either I am totally off or you are mixed in the regions and ranges

1) Create a range from "A3:I36" called "Region"
>>>> In what sheet workbook ? Presume in 'Daily GSFI' if yes then region is "A3:I37" and not 36 Correct ?

2) Open "WTD Commentary" -> put a formula in F45 to F56
>>>> Again if we look at sheet WTD Commentary we have data from F45 to F66 so is this the Range or .... ????

gowflow
Furthermore,

3) The file you posted if we are to compare the data between WTD Commentary in Row F45 downward and trying to find these values in sheet 'Daily GSFI'  Col I, then it is almost impossible the one and only amount that correspond is 81,900.00 that is the first item and that we can find in E45 and not F45.

Can you please explain the logic of what you are trying to achieve and please leave the filename issue aside and the possible solutions. As we need to understand first what you want and not how you envision resolving your problem.

Regards
gowflow
You could make a copy of the current daily file as DailyGSFI.xlsm and then use the formula you posted.  This could possibly be done in a batch file that opens/launches the WTDCommentary workbook.  It might be possible to make the copy in VBA code in the Workbook_Open() event in the WTDCommentary workbook.  I'm less sure about the success of the second method.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Justincut
Justincut

ASKER

Hi, this is the logic. I have to do a Vlookup up from the WTD Commentary spreadsheet to update the Book Value of the Companies in Column B. The spreadsheet I do the lookup to is opened via an e-mail and not saved down to any path. Thus, done manually, I have to do a Vlookup to Column D in the GSFI spreadsheet in Column D and go across to the USD MTM Position column. The GSFI PL has a variable ending too so it could have yesterday's date on the end.using VBA from a Macro in the WTD commentary file, I want to create a Range with which to do a Vlookup in the GSFI file so I can see the formulas in the WTD Commentary file instantly by pushing a Macro. Any ideas?
The Range called Region I want in the GSFi file, but it must be recognized in the Vlookup formula in the WTD Commentary file.the formulas go in the F45 Cells downwards so it should look it =Vlookup(B45, Region, 12, False). Ignore the fact they are already populated.
Hi Guys, this line is breaking in the Macro in the "WTD Commentary" spreadsheet.

 ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[" & myfilename & "]" & MYSHEETNAME & "'!" & myrangename & ",16,FALSE)"

I am getting "Run-time error '1004' Application defined error. Any ideas why?
WTD-Commentary-17112014.xlsm
DAILY-GSFI-PL-20141107.xlsx