Error getting a named range from another sheet in the same workbook

Question

I have a workbook with multiple sheets. In the name manager, I have a range that looks like this:

Name                          Value               Refers To              
PremierPeeled                 "a","b","c",etc     =BrandPeeledSizes!$C$1:$U$1 

where BrandPeeledSizes is the sheet name. When I work in the name manager, it populates the value column just fine. My primary sheet is called Master and when I change data in one of two cells on Master, I build the name of the named range I need to copy into the Master sheet. My call looks like this:

    Range("SizeHeader") = Range(strCartonType) 

SizeHeader refers to a named range on the Master sheet =Master!$E$7:$W$7.

And in this test case, strCartonType = "PremierPeeled" which refers to the name range that specifies the sheet.

I've also tried this as well
Range(strCartonType).Copy Range("SizeHeader")

and get the same error

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

I've also tried just defining a range called rngTest and tried the same ways of setting the values and get the same error. It must have something to do with the way I'm specifying the range in the other sheet, but I can't figure it out.

Thanks in advance for any help. Lynn

I tried a number of things. All in the above description.

Answer 1

Using Named Ranges

• In the workbook containing the code, the following will delete any defined name that contains SizeHeader or PremierPeeled (case ignored) and will recreate these names (of workbook scope) for the corresponding ranges of the corresponding sheets (see the arrays at the top), i.e., the created name SizeHeader will refer to the range Master!E7:W7 while the created name PremierPeeled will refer to the range BrandPeeledSizes!C1:U1.

Sub RepairNames()
    
    Dim SheetNames() As Variant: SheetNames = VBA.Array( _
        "Master", "BrandPeeledSizes")
    Dim RangeNames() As Variant: RangeNames = VBA.Array( _
        "SizeHeader", "PremierPeeled")
    Dim RangeAddresses() As Variant: RangeAddresses = VBA.Array( _
        "E7:W7", "C1:U1")
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim Limit As Long: Limit = UBound(RangeNames)
    
    Dim nm As Name, CurrentName As String, RequiredName As String, i As Long
    
    For Each nm In wb.Names
        CurrentName = nm.Name
        For i = 0 To Limit
            RequiredName = RangeNames(i)
            If InStr(1, CurrentName, RequiredName, vbTextCompare) > 0 Then
                nm.Delete
                Exit For
            End If
        Next i
    Next nm
    
    For i = 0 To Limit
        wb.Sheets(SheetNames(i)).Range(RangeAddresses(i)).Name = RangeNames(i)
    Next i

End Sub

• Now you should be able to use any of the following codes.

Recommended (Educational)

Sub TestRangeVariables()

    Dim strCartonType As String: strCartonType = "PremierPeeled"
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim sws As Worksheet: Set sws = wb.Sheets("BrandPeeledSizes")
    Dim srg As Range: Set srg = sws.Range(strCartonType)
    
    Dim tws As Worksheet: Set tws = wb.Sheets("Master")
    Dim trg As Range: Set trg = tws.Range("SizeHeader")
    
    trg.Value = srg.Value
    
End Sub

Recommended (Short)

Sub TestRange()

    Dim strCartonType As String: strCartonType = "PremierPeeled"
    
    With ThisWorkbook
        .Sheets("Master").Range("SizeHeader").Value = _
            .Sheets("BrandPeeledSizes").Range(strCartonType).Value
    End With

End Sub

Not Recommended

• Useful when the sheet names are unknown. Doesn't work for worksheet-scoped names.

Sub TestName()

    Dim strCartonType As String: strCartonType = "PremierPeeled"
    
    With ThisWorkbook
        .Names("SizeHeader").RefersToRange.Value = _
            .Names(strCartonType).RefersToRange.Value
    End With

End Sub