VBA Code - Formula from one workbook to another

[Mai_Mai]

Hi,

Need help. I am an excel macro newbie. I want this formula from my current workbook to lookup for values of G4 to another workbook. The another workbook file name is in the current workbook A1 cell. Here's my code.

Dim otherWB As Workbook

Set otherWB = Workbooks(Sheets("Pivot").Range("a1").Value)

Sheets("Pivot").Select
With Range("H4")
.Formula = "=IF(G4<>"""",IFERROR(VLOOKUP(G4,'[sourceWB]Detail'!E:J,1,FALSE),""For Addition""),"""")"
.Copy Destination:=Range(.Offset(0, -1), .Offset(0, -1).End(xlDown)).Offset(0, 1)
End With

The formula seems to work fine but the problem is the UpdateValuestherWB dialog box is keep on prompting to locate my file even the file is already open. How can I elimanate the prompting of the updatevalues dialog box?

Thanks in advance.

Regards,
MaiMai

 

[strooman]

The formula seems to work fine but the problem is the UpdateValuestherWB dialog box is keep on prompting to locate my file even the file is already open. How can I elimanate the prompting of the updatevalues dialog box?

Perhaps take a look at:
File | Options | Advanced | Scroll down and disable (uncheck) the reference to other workbooks or external references.

 

[Mai_Mai]

Perhaps take a look at:
File | Options | Advanced | Scroll down and disable (uncheck) the reference to other workbooks or external references.

Thanks for the help strooman, but it doesn't work.

I stand corrected with my formula it should be

.Formula = "=IF(G4<>"""",IFERROR(VLOOKUP(G4,'[otherWB]Detail'!E:J,1,FALSE),""For Addition""),"""")"

forgot to change it when I paste the code.