Hi

I have a macro that in a loop creates a sheet, called rngName, then calculates a value on this sheet (amongst other things).
This value has to then be copied to another sheet, call it Numbers.

The loop then loops to the next sheet it creates (rngName+1), etc.

I can copy the value so ---
Numbers.Range("B2").value=rngName.Range("Q10").value

but if the value on rngName sheet is changed, I would like this reflected in the Numbers sheet, as it would if you manually entered the cell reference '=rngName!Q10' into the cell Numbers!B2 in Excel for example.
I thought I could do this by using a formula---

Numbers.Range("B2").formula= "=rngName!Q10"

but I don't know how to format using a variable sheet name in a function.

Any other suggestions as to how to, what is essentially just referencing one cell to another, not the value or the formula, would be very appreciated.