Copy info of a name range

[snb]

Referstorange doesn't apply to a noncontiguous range; use:

[vba]
Sub M_snb()
Sheets("Sheet2").Range("A1:A4,C1:E4,G1:H4").Name = "snb_002"
For Each ar In Split(Mid(ThisWorkbook.Names("snb_002").RefersTo, 2), ",")
Sheets("Sheet3").Range(Split(ar, "!")(1)).Value = Evaluate(ar).Value
Next
End Sub
[/vba]