Macro to Copy to Last Row of Data

[spyldbrat]

How do I edit this to copy the contents in cells P2 and Q2 the last row of data in both columns P & Q? I know where my problem below...I just don't know how to fix it.

Thanks

Range("p2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13],Collector,3,FALSE),"" "")"
Range("p2").Select
Selection.AutoFill Destination:=Range("p211")
Range("p211").Select
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-12]),"" "",VALUE(RC[-12]))"
Range("Q2").Select

Selection.AutoFill Destination:=Range("Q2:Q11")
Range("Q2:Q11").Select
Columns("Q:Q").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Fluff]

Is there already data in Cols P & Q, that you're overwriting?
If not what col can be used to calculate the last row of data?

 

[spyldbrat]

Columns P and Q are empty with the exception of the header row on row 1. The above puts a formula in cells P2 and Q2 and I need those two formulas to copy down to the last line of data in my spreadsheet. The next empty column I have is R.

 

[Fluff]

One option is

    Dim UsdRws As Long
    
    UsdRws = Cells.Find("*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("P2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13],Collector,3,FALSE),"" "")"
    Range("Q2").FormulaR1C1 = "=IF(ISBLANK(RC[-12]),"" "",VALUE(RC[-12]))"
    Range("P2:Q" & UsdRws).FillDown
    Range("Q1:Q" & UsdRws).Copy
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Alternatively, if Col Q is simply to convert col E to values, try

    Dim UsdRws As Long
    
    UsdRws = Cells.Find("*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("P2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13],Collector,3,FALSE),"" "")"
    Range("P2:P" & UsdRws).FillDown
    With Range("E2:E" & UsdRws)
        .Value = .Value
    End With

 

[spyldbrat]

Runs like a charm!!!! Thank you very much!

 

[Fluff]

Glad to help & thanks for the feedback