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

 

[johnnyL]

Does it work without the quotes in that line?

 

[dmt32]

try changing that line to this and see if resolves the issue

Set rng = Range("MondayE,MondayG,MondayI")

note: example assumes all three named ranges are on the same sheet


Dave

 

[johnnyL]

I think I would change the error line to:

Set rng = Union(MondayE, MondayG, MondayI)

 

[mumps]

Thank you both for your suggestions. Unfortunately, they both still produce errors.

note: example assumes all three named ranges are on the same sheet

The named ranges are on a different sheet. I tried activating the sheet before that line of code runs and it still errors out. Is there any way of handling this issue?

 

[johnnyL]

Union does require all the ranges to be on the same sheet.

 

[mumps]

I tried Union in my original post without success. All the named ranges are on a sheet called "Lists".

 

[johnnyL]

Link to how to use Union

 

[mumps]

Thanks for the link. I have used Union successfully on many occasions but using it with named ranges that are located on a different sheet is generating the error. I tried changing the scope to "Workbook" but the error is still there.

 

[Alex Blakenburg]

What is the scope of the 3 named ranges ? If any are a scope of sheet what is the sheet name they are referring to ?
What is the sheet name the event macro is on ?

eg If I run the below with Sheet1 active
- a workbook rngD
- worksheet (sheet2) rngD
- rngA & rngG workbook scope but on sheet2
it needs to look like this:

    Set rngComb = Union(Range("rngA"), Range("Sheet2!rngD"), Range("rngG"))