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