Named Range using VBA

[cfree36]

I have the following code:

Sub NameRange()
Dim WS As Worksheet

counter = 1

If counter = 51 Then Exit Sub

For Each WS In ActiveWorkbook.Worksheets
    If WS.Visible = xlSheetVisible Then
    WS.Activate
    ActiveCell.Cells.Select
    ActiveWorkbook.Names.Add Name:="vendor" & counter, RefersToR1C1:="='" & counter & "'!R1:R65536"""
    counter = counter + 1
    End If
Next WS
End Sub

However I get an error on the RefersToR1C1 line.

The counter changed each Name to Vendor1, Vendor2, etc....
The sheets are named 1, 2, 3, etc...
I need the named range to refer to the correct sheet

Vendor1 should refer to Sheet Named '1' R1:R65536
Vendor2 should refer to Sheet Named '2' R1:R65536
etc.

Any help here would be great... Thanks in advance.

 

[Norie]

Try this.

Sub NameRange()
Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Visible = xlSheetVisible Then
            counter = counter + 1
            WS.Range("A1:IV65536").Name = "vendor" & counter
        End If
    Next WS
    
End Sub

 

[cfree36]

EXCELLENT.
Works GREAT!

Thanks.