Excel VBA Add Named ranges with passed through sheet names.

[Rowland Hamilton]

How can I replace the sheet name with a passed through named variable?

MasterWB.Names("HC_Results").RefersToR1C1 = _
        "=OFFSET(Results!R1C1,0,0,COUNTA(Results!C1),COUNTA(Results:Results!R1))"

Does't work when I just do this:

MasterWB.Names("HC_" & wsR & "").RefersToR1C1 = _
        "=OFFSET(" & wsR & "!R1C1,0,0,COUNTA(" & wsR & "!C1),COUNTA(" & wsR & ":" & wsR & "!R1))"

I have the sheet defined with

Sub Update_NamedRange(wsR As Worksheet, MasterWB As Workbook)

And the code passes from:

Sub START_IT_YALL()
Dim wsR As Worksheet 'Results worksheet
Dim MasterWB As Workbook
    Set MasterWB = ThisWorkbook
    Set wsR = Worksheets("Results")
    Call Update_NamedRange(wsR, MasterWB)
End Sub

Here is full code:

Option Explicit
Option Compare Text

Function NamedRangeExists(strName As String, _
    Optional wbName As String) As Boolean
     'Declare variables
    Dim rngTest As Range, i As Long
     
     'Set workbook name if not set in function, as default/activebook
    If wbName = vbNullString Then wbName = ActiveWorkbook.Name
     
    With Workbooks(wbName)
        On Error Resume Next
         
         'Loop through all sheets in workbook.  In VBA, you MUST specify
         ' the worksheet name which the named range is found on.  Using
         ' Named Ranges in worksheet functions DO work across sheets
         ' without explicit reference.
        For i = 1 To .Sheets.Count Step 1
             
             'Try to set our variable as the named range.
            Set rngTest = .Sheets(i).Range(strName)
             
             'If there is no error then the name exists.
            If Err = 0 Then
                 
                 'Set the function to TRUE & exit
                NamedRangeExists = True
                Exit Function
            Else  'Clear the error
                Err.Clear
            End If
        Next i
    End With
End Function
 
Sub NREtest1()
    MsgBox NamedRangeExists("test")
End Sub
 
Sub NREtest2()
    MsgBox NamedRangeExists("testing")
End Sub
Sub Update_NamedRange(wsR As Worksheet, MasterWB As Workbook)
     'I want to create a Named Range if it doesn't exist already ..
    If NamedRangeExists("HC_Results") Then
        MasterWB.Names("HC_Results").RefersToR1C1 = _
        "=OFFSET(Results!R1C1,0,0,COUNTA(Results!C1),COUNTA(Results:Results!R1))"
        
        '  ActiveWorkbook.Names("HC_Results").RefersToR1C1 = _
        "=OFFSET(Results!R1C1,0,0,COUNTA(Results!C1),COUNTA(Results!R1))"
    Else
        MasterWB.Names.Add Name:="HC_Results", RefersToR1C1:= _
        "=OFFSET(Results!R1C1,0,0,COUNTA(Results!C1),COUNTA(Results!R1))"
    End If
End Sub
Sub START_IT_YALL()
Dim wsR As Worksheet 'Results worksheet
Dim MasterWB As Workbook
    Set MasterWB = ThisWorkbook
    Set wsR = Worksheets("Results")
    Call Update_NamedRange(wsR, MasterWB)
End Sub

 

[Rowland Hamilton]

When I separate with ampersands, and type in the sheet name it works but when I use the variable it does not.

This works:

MasterWB.Names("HC_Results").RefersToR1C1 = _
        "=OFFSET(" & "Results" & "!R1C1,0,0,COUNTA(" & "Results" & "!C1),COUNTA(" & "Results" & "!R1))"

But I want to use the pass through (doesn't work):

MasterWB.Names("HC_Results").RefersToR1C1 = _
        "=OFFSET(" & """ & wsR & """ & "!R1C1,0,0,COUNTA(" & "" & wsR & """ & "!C1),COUNTA(" & """ & wsR & """ & "!R1))"

 

[Rowland Hamilton]

Ah, need to assign as variable not worksheet...

 

[Rowland Hamilton]

Tried as variant, still no go...