Using Sheet Names or a varibale in Formulae

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.

Here's my code, with non-relevant stuff removed.

PHP Code: 

Sub New_series_sheets()

' Creates new sheets for each series
 
Dim rngName As Range, rngFill As Range
Dim i As Integer

Set rngName = Sheets("Series total").Range("a2")
Set rngFill = Sheets("RSQ").Range("b2")

'   Create copy of series total sheet

    Sheets("Series total").Select
    Sheets("Series total").copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "temp"
   
   Do Until rngName.Value = ""
       
       i = Sheets.Count
       Sheets.Add After:=Sheets(Sheets.Count)

     Sheets(i + 1).Name = rngName.Value
    
'   Populate with data copied from series total copy

    Worksheets("temp").Range("B2:M2").copy
    Worksheets(i + 1).Range("$B$2:$M$2").PasteSpecial xlPasteValues
    
       
'   Remove copied row

    Sheets("temp").Range("2:2").EntireRow.Delete
    Set rngName = rngName.Offset(1)
   
    
'   MAPE

    Worksheets(i + 1).Range("Q12").Formula = "=100*(SUM(ABS(B$6-B$14),ABS(C$6-C$14),ABS(D$6-D$14),ABS(E$6-E$14),ABS(F$6-F$14),ABS(G$6-G$14),ABS(H$6-H$14),ABS(I$6-I$14),ABS(J$6-J$14),ABS(K$6-K$14),ABS(L$6-L$14),ABS(M$6-M$14)))/(SUM(B$6:M$6))"
    
    
'   Copy MAPE values to RSQ sheet

    rngFill.Offset(0, 2).Value = Worksheets(i + 1).Range("Q11").Value
    rngFill.Offset(0, 5).Value = Worksheets(i + 1).Range("Q12").Value
    
        
    Set rngFill = rngFill.Offset(1, 0)


   Loop
   

'   Removes temp sheet

Application.DisplayAlerts = False
Worksheets("temp").Delete
Application.DisplayAlerts = True
 
    
End Sub 


rngName is taken from a column on another sheet and used to name each new sheet, working down the column. rngFill is the cell that the MAPE value needs to be copied to on another sheet called RSQ. I need this cell to update if I should adjust the numbers on any of the rngName sheets. At the moment it just copies the values across.

Worked like a charm. Many thanks!