Or, since you are on the activesheet and excel will AUTO fill in the sheet
name then


Range(Cells(2, "b"), Cells(lastrow, lastcol)).Name = "ThisWorksToo"


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave Peterson" wrote in message
...
I don't know what the OP really wants, but to me, this seems like a perfect
example for using a local/sheet level name--especially since the OP is
using the
same string (MyRangeName).

But if that's not what the OP wants, then my post is wrong, too <bg.

with activesheet
.range("b2",.cells(lastrow,lastcol)).name = "MyRangeName"
end with

Would create the global/workbook level name.

Peter T wrote:

I completely misread your post, sorry about that. You are not looking to
define Local names at all so don't do what I suggested below.

For Q1 go with Don's. For Q2 the loop part below is OK but again don't
define names as in that loop. In the loop if you want to refer to cells
on
the sheet don't forget to qualify with the sheet, eg
ws.cells((LastRow,LastColumn)

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Q1.
sName = ActiveSheet.Name

It looks like you want to define a Worksheet level name, sometimes
known
as
a 'Local' Name. Although you can do it the way you intend another way
is
simpley

ActiveSheet.Names.Add "LocalNameA", Range("A1")

ActiveSheet should be a worksheet, not a chartsheet

Q2.
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
'add local name to same range on each sheet
ws.Names.Add "Hello", ws.Range("B1")
Next

Regards,
Peter T


"mario" wrote in message
...
Thanks in advance for all your help. I have two questions.

Question 1:
-------------

I want to change this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersTo:= _
"=Sheet7!B2:" & Cells(LastRow, LastColumn).Address

to this:
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersTo:= _
"<VBA code for current worksheet name"&"!B2:" &
Cells(LastRow,
LastColumn).Address

I want to get the VBA code for getting the current worksheet name and
replace "sheet7" with the name of the current worksheet.

Question 2:
-------------

I want to run a vba function for all the worksheets in an excel file
(or
current workbook). I am looking to create a function some thing like
this:

For Every Worksheet
Function MakeChaneges 'Run the function with name "make changes"
Next

can you help me with the code for the "For" "Next" Loop

Thanks




--

Dave Peterson