Vlookup between 2 workbooks.

[Vickytayade18]

Hi all, I am starting my VBA journey and i have requirement of V LOOKUP between 2 files.
I have prepared a code to paste a value from "source file" into Output file, but i am not able to solve the error, which is why i need your help. Please help me to get a solution for it.

my requirement is as " When i paste part no's in "B4"onward in macro and run it, it should go to "Source file" and fetch the 4 values and gives output.

Sub HTS_TEMPLATE()

Dim PartsWS As Worksheet, dataWS As Worksheet
Dim PartsLastRow As Long, DatalastRow As Long, X As Long
Dim DataRng As Range

Set PartsWS = ThisWorkbook.Worksheets("Item number")
Set dataWS = ThisWorkbook.Worksheets("Data")


PartsLastRow = PartsWS.Range("A" & Rows.Count).End(xlUp).Row
DatalastRow = dataWS.Range("A" & Rows.Count).End(xlUp).Row

Set DataRng = dataWS.Range("A2:l" & DatalastRow)

'For X = 2 To PartsLastRow
'On Error Resume Next

 dataWS.Range("B" & X).Value = Application.WorksheetFunction.vlookup( _
 dataWS.Range("A" & X).Value, DataRng, 2, False)
 
'Next X

PartsWS.Select

Range("G4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],[SOURCE.xlsx]Sheet1!R1C1:R10C4,2,0)"
    Range("G4").Select
    Selection.AutoFill Destination:=Range("G4:G12")
    Range("G4:G12").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False





End Sub

 

[herofox]

hello .the same subject was here before ,
VBA code to vlookup between workbooks
and try this

 

[Marc L]

Hi,​

​

according to the attachment it seems there is a typo in the code :​

​

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],[SOURCE.xlsx]Sheet1!R1C1:R10C4,2,0)"​

​

As you can easily compare the formula within the sheet with the one used in the code …​

​

 

[Vickytayade18]

hello .the same subject was here before ,
VBA code to vlookup between workbooks
and try this

Hi brother, I checked the code, but i am not getting values in SAMPLE MACRO, this code is just opening the "SOURCE" file and closing it with message "there already a value do you want to replace it or not?" i tried both option but not getting out put. could you please check it.

I want values in SAMPLE MACRO file when i gave input values in B4 cell and run MACRO, it should fetch values from Source file.

i am attaching both files for your reference. I hope you don't mind helping me to correct the code.

thanks in advance

 

[Vickytayade18]

Hi,​

​

according to the attachment it seems there is a typo in the code :​

​

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],[SOURCE.xlsx]Sheet1!R1C1:R10C4,2,0)"​

​

As you can easily compare the formula within the sheet with the one used in the code …​

​

I a looking for code between 2 workbooks, this code i used earlier, but since i am new i dont know how to write code for this requirement.

 

[Marc L]

What is the working cell formula when you operate manually the worksheet ?​

​

 

[Vickytayade18]

What is the working cell formula when you operate manually the worksheet ?​

​

Currently i am doing this work manually.

 

[XcelThug]

>>> use code - tags <<<
code:

Sub vlkup()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'this is to know if the source file is open or no, late you can change the file name "Source" to orighinal file name.
Set wb = ActiveWorkbook
For Each wb In Application.Workbooks
    If wb.Name Like "*source*" Then
        Ct = Ct + 1
        wb.Activate
        Exit For
    End If
Next wb
If Ct = 0 Then MsgBox "File not open"

'we are not declaring the the SAMPLE MACRO as we are writing the code in Sample Macro book and it can be addressed with "Thisworkbook"

ThisWorkbook.Activate
Range("D4").Formula = "=IFERROR(VLOOKUP(B4,'[Source.xls]Sheet0'!$A$2:$D$10,4,0),0)"
'Autofilling the formulas down.
Range("D4").Select
Range(ActiveCell, "D" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown

Range("E4").Formula = "=IFERROR(VLOOKUP(B4,'[Source.xls]Sheet0'!$A$2:$D$10,3,0),0)"
'Autofilling the formulas down.
Range("E4").Select
Range(ActiveCell, "E" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown

Range("G4").Formula = "=IFERROR(VLOOKUP(B4,'[Source.xls]Sheet0'!$A$2:$D$10,2,0),0)"
'Autofilling the formulas down.
Range("G4").Select
Range(ActiveCell, "G" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown
   

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Vickytayade18]

>>> use code - tags <<<
code:

Sub vlkup()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'this is to know if the source file is open or no, late you can change the file name "Source" to orighinal file name.
Set wb = ActiveWorkbook
For Each wb In Application.Workbooks
    If wb.Name Like "*source*" Then
        Ct = Ct + 1
        wb.Activate
        Exit For
    End If
Next wb
If Ct = 0 Then MsgBox "File not open"

'we are not declaring the the SAMPLE MACRO as we are writing the code in Sample Macro book and it can be addressed with "Thisworkbook"

ThisWorkbook.Activate
Range("D4").Formula = "=IFERROR(VLOOKUP(B4,'[Source.xls]Sheet0'!$A$2:$D$10,4,0),0)"
'Autofilling the formulas down.
Range("D4").Select
Range(ActiveCell, "D" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown

Range("E4").Formula = "=IFERROR(VLOOKUP(B4,'[Source.xls]Sheet0'!$A$2:$D$10,3,0),0)"
'Autofilling the formulas down.
Range("E4").Select
Range(ActiveCell, "E" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown

Range("G4").Formula = "=IFERROR(VLOOKUP(B4,'[Source.xls]Sheet0'!$A$2:$D$10,2,0),0)"
'Autofilling the formulas down.
Range("G4").Select
Range(ActiveCell, "G" & Cells(Rows.Count, 2).End(xlUp).Row).FillDown
  

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

thank you so much @[B]XcelThug[/B] the macro is working fine. i made some changes as per my requirement.

thanks again for the help.

 

[global]

Hi all, I am starting my VBA and i have requirement of V LOOKUP between 2 sheet
Please help me to get a solution for it.