It doesn't look like shtSup1 has been assigned to a worksheet.

In passing, as your ranges are all exactly the same size you might, with
luck, end up pasting all four areas. Normally though you'ld only end up
pasting the 1st area. Typically you'll need to do something like this

dim rngDest as Range, ar as Range

Set shtSup1 = ActiveSheet
Set rngDest = shtSup1.Range("S43")
For Each ar In Rng.Areas
ar.Copy rngDest
Set rngDest = rngDest.Offset(, ar.Columns.Count)
Next

Regards,
Peter T

"Neecy" wrote in message
...
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!