Named Ranges: Can I do This? How?

[Walter]

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!

 

[Rick Rothstein]

Try it this way...

shtSup1.Names.Add Name:="Sup2"_BCAPA_Ratings", RefersTo:=Range( _
"Supplier!$C$6,Supplier!$C$16,Supplier!$C$23,Supplier!$C$36,Supplier!$C$56"), _
Visible:=True

Note that the Range function is used to create the RefersTo reference and that its argument is a String value (hence, the quote marks).

 

[Tom Hutchins]

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

 

[Bob Phillips]

Dim shtSup1 As Worksheet
Dim rng As Range

Set shtSup1 = Sheets("Supplier")
Set rng = shtSup1.Range("C6,C16,C23,C36,C56")
rng.Name = "Sup1_BCAPA_Ratings"

 

[Dave Peterson]

Just to add to Bob's response...

If you wanted to use a worksheet level name:

Set shtSup1 = Sheets("Supplier")
with shtSup1
Set rng = .Range("C6,C16,C23,C36,C56")
rng.Name = "'" & .name & "'!Sup1_BCAPA_Ratings"
end with

 

[Walter]

Can I request the name of the tab in this formula instead of putting the word
Supplier in it: NameOfTab!$C$6 What would I enter in place of "NameOfTab"
to have the range automatically include the tab name in the formula for all
cell references?

 

[Neecy]

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("D4453")
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-

 

[Peter T]

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