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:


    But this is what I get;


    Not sure why, Can anyone help??

    Thank you


    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

  • You need to create the string using the variable contenets, not the vaiable name, e.g.:

    ActiveWorkbook.Names.Add Name:=RSource, RefersTo:="=OFFSET('" & SheetName & "'!$A$6,0,0,COUNTA('" & SheetName & "'!$A:$A),1)"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!