How to use vlookup and copy paste value on excel VBA

[ExcelPupper]

Hello. To begin with, what I need is to have to lookup the needed data from another file.
I have here the screenshot of the result of lookup and paste values when I do it manually.

Kindly check my code below for corrections. Thanks!

Sub LookUpData()
'
'
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    
    Application.ScreenUpdating = False
    Dim MyFile As String, wb As Workbook

    MyFile = "C:\Users\ADMIN\Desktop\vba\SourceData.xlsm"
    
    
    Set wb = Workbooks.Open(MyFile)
    
    Workbooks("TestData").Sheets("Sheet1").Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,2,FALSE)"
    Range("C2").Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,3,FALSE)"
    Range("C2:D2").Select
    Selection.AutoFill Destination:=Range("C2:D4")
    Range("C2:D4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Joe4]

I think we need a detailed explanation of exactly what it is you are trying to do, and what about your code isn't working.

 

[ExcelPupper]

Sorry for having incomplete data on my earlier post, so here's a brief explanation on what I'm trying to code.
I have this file which I need to use VLOOKUP function everytime and copy-paste value it so the formula won't add to the file size.
Because it is routine work, I thought that I could make a macro for it but I guess I have to do some modifications on the code that is created by recording.

This first file is where I created the macro for automatic VLOOKUP and have it copied down to last row with data.

This other file is where the data for VLOOKUP comes from, I use the InvoiceNo as the lookup_value.

Sub LookUpData()
'
'
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    
    Application.ScreenUpdating = False
    Dim MyFile As String, wb As Workbook

    MyFile = "C:\Users\ADMIN\Desktop\vba\SourceData.xlsm"
    
    
    Set wb = Workbooks.Open(MyFile)
    
    Workbooks("TestData").Sheets("Sheet1").Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,2,FALSE)"
    Range("C2").Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,3,FALSE)"
    Range("C2:D2").Select
    Selection.AutoFill Destination:=Range("C2:D4")
    Range("C2:D4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Kindly refer to code above thanks!

 

[Joe4]

See if this does what you want:

Sub LookUpData()
' Keyboard Shortcut: Ctrl+Shift+W
'
    
    Application.ScreenUpdating = False
    
    Dim MyFile As String, wb As Workbook
    Dim lr As Long

    MyFile = "C:\Users\ADMIN\Desktop\vba\SourceData.xlsm"
    
    Set wb = Workbooks.Open(MyFile)
    
'   Find last row with data in column A of Sheet1 in TestData file
    Workbooks("TestData").Activate
    Sheets("Sheet1").Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Populate column C with formulas, then copy over values
    Range("C2:C" & lr).FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C:D,2,FALSE)"
    Range("C2:C" & lr).Value = Range("C2:C" & lr).Value
    
'   Populate column D with formulas, then copy over values
    Range("D2:D" & lr).FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C:E,3,FALSE)"
    Range("D2:D" & lr).Value = Range("D2:D" & lr).Value

    Application.ScreenUpdating = True
    
End Sub

 

[ExcelPupper]

See if this does what you want:

Sub LookUpData()
' Keyboard Shortcut: Ctrl+Shift+W
'
   
    Application.ScreenUpdating = False
   
    Dim MyFile As String, wb As Workbook
    Dim lr As Long

    MyFile = "C:\Users\ADMIN\Desktop\vba\SourceData.xlsm"
   
    Set wb = Workbooks.Open(MyFile)
   
'   Find last row with data in column A of Sheet1 in TestData file
    Workbooks("TestData").Activate
    Sheets("Sheet1").Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Populate column C with formulas, then copy over values
    Range("C2:C" & lr).FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C:D,2,FALSE)"
    Range("C2:C" & lr).Value = Range("C2:C" & lr).Value
   
'   Populate column D with formulas, then copy over values
    Range("D2:D" & lr).FormulaR1C1 = "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C:E,3,FALSE)"
    Range("D2:D" & lr).Value = Range("D2:D" & lr).Value

    Application.ScreenUpdating = True
   
End Sub

Hello. When I try to use "Step Into" to see how the code works, I get this on formula for Vlookup: "=VLOOKUP($A2,[SourceData.xlsm]Sheet1!C:C:D,2,FALSE)"
I just want to confirm why does the table array contains "C:C:D"? But in the code is contains only "C:D"

 

[Fluff]

The formulae should be
"=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,2,FALSE)"
and
"=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,3,FALSE)"

 

[ExcelPupper]

The formulae should be
"=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,2,FALSE)"
and
"=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,3,FALSE)"

Wow it works, thanks to you.
I also did the Step Into to see how it works. However, I just want to know the reason why this formula "=VLOOKUP(RC1,[SourceData.xlsm]Sheet1!C3:C5,2,FALSE)" on vba code is the same as =VLOOKUP($A2,[SourceData.xlsm]Sheet1!$C:$E,2,FALSE) on excel?
See attached below for reference.

 

[Fluff]

The R1C1 notation is just a different way of referring to a cell
RC1 means column 1 on the row containing the formula and C3:C5 is columns 3 to 5 (ie C:E)

 

[ExcelPupper]

The R1C1 notation is just a different way of referring to a cell
RC1 means column 1 on the row containing the formula and C3:C5 is columns 3 to 5 (ie C:E)

Does it mean that RC1 on vba code is equal to A2 on excel? (but on my understanding A2 is equal to R2C1?)
Kindly enlighten me ?

 

[Fluff]

Does it mean that RC1 on vba code is equal to A2 on excel?

NO. it is column A on the SAME row as the formula, because there is no value for the row portion.
R = same row
R1 =row 1
R[1] = one row below.

but on my understanding A2 is equal to R2C1?

You understand correctly, but that will "lock" the formula to look at A2, rather than moving to A3, A4 etc.