cfree36
Board Regular
- Joined
- Oct 5, 2005
- Messages
- 175
I have the following code:
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.
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.