How to create a dynamic range for vlookup based on active cell

[Torrino1]

Below is my code thus far. I am working to input RTF data into Excel and then run a Vlook against that data to populate a new range each day. As you can see my current code is specific to a set column and I am hoping to write code to have the range be dynamic based on the active cell for the auto-fill.

    Sheets("Apr 18").Select
    '
   ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Monthly Data'!C1:C2,2,FALSE)"
    Selection.AutoFill Destination:=Range("E3:E600")
    Range("E3:E600").Selec
    ActiveSheet.Range("$A$2:$HW$600").AutoFilter Field:=5, Criteria1:="#N/A"
    Selection.ClearContents
    ActiveSheet.Range("$A$2:$HW$600").AutoFilter Field:=5
    Columns("C:AG").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "1"
    Sheets("Monthly Data").Select
    Columns("A:A").Select
    Selection.QueryTable.Delete
    Selection.ClearContents
    Sheets("Apr 18").Select
 
    
End Sub

[SamT]

Moderator Bump