Hmmmm.... Don't know what I did because it's working now and i didn't change
any code.... thanks guys!

J

"Gary Keramidas" wrote:

i use this code i wrote to help me with naming ranges. make a test range on
a sheet and put his on the sheet code page. it will put the syntax in the
immediate window


Sub name_ranges2() ' this creates the named range
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & nm.Name & """" & _
", Refersto:=""" & "=" & sName & "!" & Range(nm).Address & """"
Next nm
End Sub

--


Gary


"Gixxer_J_97" wrote in message
...
Hi all,

i am using the following function to
1) Create a named range
2) return the name of that range

i am having a problem with the line:

ActiveWorkbook.names.Add name:=sName, RefersToR1C1:=sAddr

the error is:
Run-time error '1004':
The name is not valid

Public Function GetPageRange(m As String, p As String, c As Integer,
Optional t As String) As String
With Sheets(p)
Dim rng As Range, rng1 As Range, rng2 As Range, sAddr As String,
sName As String
Set rng = Columns(c).Find(m)
sAddr = rng.Address
If Not rng Is Nothing Then
Do
Set rng1 = rng
Set rng = Columns(c).FindNext(rng)
Loop While rng.Address < sAddr
End If
Set rng2 = Range("B3")
Do
Set rng2 = rng2.Offset(0, 1)
Loop While rng2.Value < ""
On Error Resume Next
ActiveWorkbook.names(p & "_" & t).Delete
On Error GoTo 0
sName = p & "_" & t
sAddr = "='" & p & "'!R" & rng.Row & "C2:R" & rng1.Row & "C" &
rng2.Column - 1
ActiveWorkbook.names.Add name:=sName, RefersToR1C1:=sAddr
GetPageRange = p & "_" & t
End With
End Function

any thoughts?

tia!

J