Dynamic Cell Reference in a VBA FormulaR1C1

Question

I am trying to add a R1C1Formula in a Cell of a newly created Worksheet with VBA. While creating the Sheet, i fill it with lot's of Formulas and standard Data. The whole looping and referencing works perfectly fine. But now i ended up with a real struggle. My Reference consists of three parts, but i can't connect them. Those are:

• the previous Worksheet

• the last row

• the last column of the last row

    pWks = Worksheets(Sheets.Count - 1)
    lRow = pWks.Cells(Rows.Count, 9).End(xlUp).row
    lCol = pWks.Cells(lRow, Columns.Count).End(xlToLeft).column
  

It should be something like:

Cells(r, z + 9).FormulaR1C1 = "=pWks! R lRow C lCol"

EDIT: The solution is: make the pWks Variable a string instead of a Object or Worksheet. Then the Formula looks like:

pWks = Worksheets(Sheets.Count - 1).Name
Cells(r, z + 9).FormulaR1C1 = "=" & pWks & "!R" & lRow & "C" & lCol

but how can i connect those three parts into the VBA Syntax? Excel won't let me do the proposed way...

Until now, i simply copy the Value of the Cell into the new one, but that is not what i wanna achieve. As with this method, the Value doesn't get updated when i change something.

    Cells(r, z + 9).Value = Worksheets(Sheets.Count - 1).Cells(lRow, lCol).Value