I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"


--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...
I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Tom Ogilvy wrote:
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

Thanks, Tom, worked like a charm, as always.



--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...
I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

see if this works?

Sub namerange()
Range("b3").CurrentRegion.Name = "rCtyShtFltRng"
End Sub

--
Don Guillett
SalesAid Software

"davegb" wrote in message
oups.com...
I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Don Guillett wrote:
see if this works?

Sub namerange()
Range("b3").CurrentRegion.Name = "rCtyShtFltRng"
End Sub


Thanks, Don, but I chose the column and row to determine the range
because I have blanks and merged cells in the sheet.

--
Don Guillett
SalesAid Software

"davegb" wrote in message
oups.com...
I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Sub CtyShtFltRng()

Dim lCol As Long
Dim lRow As Long
Dim wActSheet As Worksheet
Dim rStart As Range
Dim rCtyShtFltRng As Range

Set wActSheet = ActiveSheet
Set rStart = Range("B3")

lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column

Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))

rCtyShtFltRng.Name = "FilterRange"
wActSheet.Range("FilterRange").Select

End Sub


RBS


"davegb" wrote in message
oups.com...
I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

I think the problem was that you were setting the worksheet object to be a
named range

Try this

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rCtyShtFltRng As Range

Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
ActiveWorkbook.Names.Add Name:="FilterRange",
RefersToR1C1:=rCtyShtFltRng
Range("FilterRange").Select
End Sub


"davegb" wrote in message
oups.com...
I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Your syntax needs a little tweaking. Also since you declare the worksheet
object you should be sure to reference it in all of you Range and Cells
references. It does not make a difference in this case since it is the active
sheet but it is a good habit to get into... I also declared your range
objects assuming that they were not declared publicly somewhere else. If they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub
--
HTH...

Jim Thomlinson


"davegb" wrote:

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Jim Thomlinson wrote:
Your syntax needs a little tweaking. Also since you declare the worksheet
object you should be sure to reference it in all of you Range and Cells
references. It does not make a difference in this case since it is the active
sheet but it is a good habit to get into... I also declared your range
objects assuming that they were not declared publicly somewhere else. If they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub
--
HTH...

Jim Thomlinson

Thanks for your reply, Jim. This is very interesting and making me
curious. The code Tom sent me worked fine, on 6 of the 9 sheets that
call this routine when they are activated. But on 3 of the sheets, it
was hanging up, with a "Wrong number of arguments or invalid property
assignment" error on the line

rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

I was struggling with figuring out why when you gave your reply. I
tried your code and it works on all 9 sheets. It must have something to
do with "referencing it in all of your range and cell references". But
why would it work on some sheets but not on others?
I think you're referring to the periods you placed in that line
referencing back to the "with wActSheet" statement. Somehow, some of
the sheets figured out which sheet I was referring to, and the others
couldn't. Very strange. Do you, or anyone else, have any ideas on this?
As to your reference about declaring the variables, some of them are
declared publicly, as you surmised.



"davegb" wrote:

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

I suspect the names of the sheets that fail have spaces in the sheet name.
So adjust to

rCtyShtFltRng.Name = "'" & wActSheet.Name & "'!FilterRange"

so that the sheet name will be enclosed in single quotes

'my sheet'!FilterRange

It will still work on the already working sheets.

Jim avoids the problem by using Names.Add with an object referencing the
sheet as a qualifier, so it is implicitely added as a sheet level name.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Jim Thomlinson wrote:
Your syntax needs a little tweaking. Also since you declare the
worksheet
object you should be sure to reference it in all of you Range and Cells
references. It does not make a difference in this case since it is the
active
sheet but it is a good habit to get into... I also declared your range
objects assuming that they were not declared publicly somewhere else. If
they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub
--
HTH...

Jim Thomlinson

Thanks for your reply, Jim. This is very interesting and making me
curious. The code Tom sent me worked fine, on 6 of the 9 sheets that
call this routine when they are activated. But on 3 of the sheets, it
was hanging up, with a "Wrong number of arguments or invalid property
assignment" error on the line

rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

I was struggling with figuring out why when you gave your reply. I
tried your code and it works on all 9 sheets. It must have something to
do with "referencing it in all of your range and cell references". But
why would it work on some sheets but not on others?
I think you're referring to the periods you placed in that line
referencing back to the "with wActSheet" statement. Somehow, some of
the sheets figured out which sheet I was referring to, and the others
couldn't. Very strange. Do you, or anyone else, have any ideas on this?
As to your reference about declaring the variables, some of them are
declared publicly, as you surmised.



"davegb" wrote:

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations
on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Tom Ogilvy wrote:
I suspect the names of the sheets that fail have spaces in the sheet name.

Exactly!

So adjust to

rCtyShtFltRng.Name = "'" & wActSheet.Name & "'!FilterRange"

so that the sheet name will be enclosed in single quotes

'my sheet'!FilterRange

It will still work on the already working sheets.

Jim avoids the problem by using Names.Add with an object referencing the
sheet as a qualifier, so it is implicitely added as a sheet level name.

Thanks again. Can you elaborate on this? What does "object referencing
the sheet as a qualifier" mean? What is a "sheet level" name?


--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Jim Thomlinson wrote:
Your syntax needs a little tweaking. Also since you declare the
worksheet
object you should be sure to reference it in all of you Range and Cells
references. It does not make a difference in this case since it is the
active
sheet but it is a good habit to get into... I also declared your range
objects assuming that they were not declared publicly somewhere else. If
they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub
--
HTH...

Jim Thomlinson

Thanks for your reply, Jim. This is very interesting and making me
curious. The code Tom sent me worked fine, on 6 of the 9 sheets that
call this routine when they are activated. But on 3 of the sheets, it
was hanging up, with a "Wrong number of arguments or invalid property
assignment" error on the line

rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

I was struggling with figuring out why when you gave your reply. I
tried your code and it works on all 9 sheets. It must have something to
do with "referencing it in all of your range and cell references". But
why would it work on some sheets but not on others?
I think you're referring to the periods you placed in that line
referencing back to the "with wActSheet" statement. Somehow, some of
the sheets figured out which sheet I was referring to, and the others
couldn't. Very strange. Do you, or anyone else, have any ideas on this?
As to your reference about declaring the variables, some of them are
declared publicly, as you surmised.



"davegb" wrote:

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations
on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

Bob Phillips has a discussion of name on his site:

http://www.xldynamic.com/source/xld.Names.html

Dim sh as Worksheet
set sh = Activesheet
msgbox sh.name

sh is a reference to a worksheet.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...

Tom Ogilvy wrote:
I suspect the names of the sheets that fail have spaces in the sheet
name.

Exactly!

So adjust to

rCtyShtFltRng.Name = "'" & wActSheet.Name & "'!FilterRange"

so that the sheet name will be enclosed in single quotes

'my sheet'!FilterRange

It will still work on the already working sheets.

Jim avoids the problem by using Names.Add with an object referencing the
sheet as a qualifier, so it is implicitely added as a sheet level name.

Thanks again. Can you elaborate on this? What does "object referencing
the sheet as a qualifier" mean? What is a "sheet level" name?


--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Jim Thomlinson wrote:
Your syntax needs a little tweaking. Also since you declare the
worksheet
object you should be sure to reference it in all of you Range and
Cells
references. It does not make a difference in this case since it is
the
active
sheet but it is a good habit to get into... I also declared your
range
objects assuming that they were not declared publicly somewhere
else. If
they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub
--
HTH...

Jim Thomlinson

Thanks for your reply, Jim. This is very interesting and making me
curious. The code Tom sent me worked fine, on 6 of the 9 sheets that
call this routine when they are activated. But on 3 of the sheets, it
was hanging up, with a "Wrong number of arguments or invalid property
assignment" error on the line

rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

I was struggling with figuring out why when you gave your reply. I
tried your code and it works on all 9 sheets. It must have something
to
do with "referencing it in all of your range and cell references". But
why would it work on some sheets but not on others?
I think you're referring to the periods you placed in that line
referencing back to the "with wActSheet" statement. Somehow, some of
the sheets figured out which sheet I was referring to, and the others
couldn't. Very strange. Do you, or anyone else, have any ideas on
this?
As to your reference about declaring the variables, some of them are
declared publicly, as you surmised.



"davegb" wrote:

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the
determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12
variations
on
this line, but none worked. Can someone tell me how to make this
work?
Thanks!