Using Variable in Cell Formula as part of For Next

I have 2 sheets, Sheet1 and Sheet2

I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change.

So if i = 5, I'd like the cell to be populated with the formula
=Sheet1!B5


So I have the following code

Dim LastRowSheet1 as Integer
Dim LastRowSheet2 as Integer
Dim i as Long


For i = LastRowSheet1 To 2 Step -1


Sheets("Sheet1").Range("A" & i).Copy ' Copy data from column A in Sheet 1

Sheets("Sheet2").Range("A" & LastRowSheet2).PasteSpecial Paste:=xlValues ' Paste into column A of Sheet 2


Sheets("Sheet2").Range("B" & LastRowSheet2).FormulaR1C1 = "=Sheet1!R[" & i & "]C[0]" ' Populate cell with formula

LastRowSheet2 = LastRowSheet2 + 1 ' add one to count to ensure that next iteration of i populates the next row

Next i

However, something clearly isn't working right.


- Firstly, from what I can see, the FormulaR1C1 takes the numbers in R[] and C[] as relative offsets. So R[1] if I'm in row 1, would point to row 2. Is my understanding here correct? And if so, how to I get it to just point to row i based on the variable value?

- Secondly, there is something else wrong in what I've done in that the cells this macro is populating column B with the same formula in every cell (so each cell in column B is pointing to B13)

"=Sheet1!B13"