I am having problems with the merged cells values writting out to a specified
destination. Here is my code below.

Sub Import()
Dim shtSup1 As Worksheet
Dim field1 As Range, field2 As Range, field3 As Range, field4 As Range,
Rng As Range
Set field1 = Range("F44:F53")
Set field2 = Range("H44:H53")
Set field3 = Range("D44:D53")
Set field4 = Range("B44:B53")
Set Rng = Union(field1, field2, field3, field4)
Rng.Copy Destination:=shtSup1.Range("S43")
End Sub

Please review and let me know what I am doing wrong.

Thank you-

"Tom Hutchins" wrote:

Maybe something like

Sub BBB()
Dim shtSup1 As Worksheet, Rng As Range
Set shtSup1 = Sheets("Supplier")
Set Rng = Union(shtSup1.Range("C6"), shtSup1.Range("C16"), _
shtSup1.Range("C23"), shtSup1.Range("C36"), _
shtSup1.Range("C56"))
shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", RefersTo:=Rng, Visible:=True
Application.Goto Reference:="Sup1_BCAPA_Ratings"
End Sub

Hope this helps,

Hutch

"Walter" wrote:

shtSup1.Names.Add Name:="Sup1_BCAPA_Ratings", _
RefersTo:=Supplier!$C$6,Supplier!$C$16,Supplier!$C $23,Supplier!$C$36, _
Supplier!$C$56,Visible:=True

I want to add named ranges in my workbook with VBA. However, the range that
I have is discontiguous as noted above. I can enter it in the
Insert/Name/Define and it works. How do I write the code so that it compiles
in VBA? I keep getting an error. Supplier is the name of the worksheet. I
am working in Excel 2003. Thank you!