Define dynamic variables in VBA (name manager)

I setup a sheet after the last sheet with name 'SheetName'. This new sheet will have name in the form of '2254121152' as string. This code will change depending on various variable selections in the program. I would then like to define a dynamic variable which would then be used to display a dynamic data range in a list box. Code below is how I have done this, however the Ref. in the offset function will not set as I expect it to do so! In the name manager I should see a dynamic variable set up as:

Spec2254121152=OFFSET('2254121152'!$A$6,0,0,COUNTA('2254121152'!$A:$A),1)

But this is what I get;

Spec2254121152=OFFSET(sheetname!$A$6,0,0,COUNTA(sheetname!$A:$A),1)

Not sure why, Can anyone help??

Thank you

Code;

Private Sub NewDataSheet(SheetName)
Dim RSource As String

RSource = "Spec" & SheetName ' System specification dymanic list (update Raw Source)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = SheetName 'New sheet with index name
ActiveWorkbook.Names.Add Name:=RSource, RefersTo:="=offset('sheetname'!$A$6,0,0,counta('sheetname'!$A:$A),1)" 'New Raw source with sheet name as reference
Sheets(SheetName).Range("A3:S3").Value = Sheets("storage").Range("A3:S3").Value 'Copy updated system specifications record to new sheet

End Sub

Thank you for you help, this works well.