Union of named ranges not working

[mumps]

I have three named ranges as seen in the macro below. The macro generates the error: Run-time error '1004': Method' Range' of object' _Worksheet' failed with the line in yellow highlighted on Debug. Would anyone have any suggestions as to the cause of the error?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim fnd As Range, rng As Range
    Set rng = Union(Range("MondayE"), Range("MondayG"), Range("MondayI"))
    If Not Intersect(Target, rng) Is Nothing Then
        Set fnd = Sheets("Lists").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Resize(2).Copy Range(Target.Address)
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = False
End Sub

 

[RoryA]

Your code is in a worksheet_Change event so activating the other sheet will make no difference. You have to qualify any range that is not on the worksheet containing the code with its parent sheet.

 

[mumps]

I have to apologize to all those members who have responded. I had a long day yesterday and I wasn't thinking properly. The named ranges are not on a different sheet. They are on the same sheet as the worksheet_Change event. I get the same error even if I don't use Union as in the code below:

Set rng = Range("MondayE")

 

[Alex Blakenburg]

Hopefully Rory will have some other insights, since it works fine for me.
Change event is in Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngComb As Range
    Set rngComb = Union(Range("rngA"), Range("rngD"), Range("rngG"))
    
    Debug.Print rngComb.Address(external:=True)
End Sub

 

[mumps]

I believe that I have solved my problem. Upon further examination, I found that one of the three named ranges was not highlighting the cells in the named range which indicated an error in the range. I recreated that named range and it is now working properly. Thanks to all for your suggestions and time spent.