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

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

Using some test values, it worked for me:

set rng = Range("A10")
set rng1 = Range("A20")
set rng2 = Range("F1")
p = "Sheet3"
sAddr = "='" & p & "'!R" & rng.Row & "C2:R" & rng1.Row & "C" &
rng2.Column - 1
? sAddr
='Sheet3'!R10C2:R20C5
sName = "MyName"
ActiveWorkbook.names.Add name:=sName, RefersToR1C1:=sAddr
? Range(sName).Address(0,0,xlA1,True)
[Book2]Sheet3!B10:E20

If rng2 is in Column A, you would have a problem.

--
Regards,
Tom Ogilvy



"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

i have this rng:
lngMaxRow = Range("A65536").End(xlUp).Row

Set rng = Union(Range("A3:K" & lngMaxRow), Range("N3:AC"
lngMaxRow)).SpecialCells(xlCellTypeVisible)

i would want to copy the first block a3:k of current sheet into a3:k o
ActiveWorkbook.Sheets(1)

i would want to copy the second block n3:ac of current sheet into n3:a
of ActiveWorkbook.Sheets(1)

i have used this but not work!!!!!!!!:

rng.Copy ActiveWorkbook.Sheets(1).Range("A3"

--
sal2

-----------------------------------------------------------------------
sal21's Profile: http://www.excelforum.com/member.php...nfo&userid=204
View this thread: http://www.excelforum.com/showthread.php?threadid=49160

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