I want to apply the Vlookup function on a column, being the table_array from another sheet in my workbook which is dynamic.

My code works but it's not giving back any values.

Consider only the part in bold of the code:

Private Sub CommandButton3_Click()

Dim src As Worksheet
Dim tgt As Worksheet
Dim com As Worksheet
Dim filterRange As Range
Dim copyRange As Range
Dim copyRange2 As Range
Dim copyRange3 As Range
Dim calcRange As Range

Dim Lastrow As Long


Set src = ThisWorkbook.Sheets("Importação")
Set tgt = ThisWorkbook.Sheets("Status")
Set com = ThisWorkbook.Sheets("Ficheiro Endesa")


src.AutoFilterMode = False


Lastrow = src.Range("B" & src.Rows.Count).End(xlUp).row


Set filterRange = src.Range("B9:N" & Lastrow)

Set copyRange = src.Range("B10:C" & Lastrow)

Set copyRange2 = src.Range("G10:G" & Lastrow)

Set copyRange3 = src.Range("K10:K" & Lastrow)


filterRange.AutoFilter Field:=5, Criteria1:="="


copyRange.SpecialCells(xlCellTypeVisible).Copy
tgt.Range("B21").PasteSpecial xlPasteValues


copyRange2.SpecialCells(xlCellTypeVisible).Copy
tgt.Range("D21").PasteSpecial xlPasteValues


copyRange3.SpecialCells(xlCellTypeVisible).Copy
tgt.Range("E21").PasteSpecial xlPasteValues



filterRange.AutoFilter Field:=5




Lastrow = com.Range("B" & com.Rows.Count).End(xlUp).row


Set calcRange = com.Range("D8:G" & Lastrow)


Range("G21").Select
ActiveCell.FormulaR1C1 = _
"=+IFERROR(VLOOKUP(RC[-5],calcRange,3,FALSE),""-"")"

Range("G21").Select
Range("G21").AutoFill Destination:=Range("G21:G" & Cells(Rows.Count, "B").End(xlUp).row)

End Sub