VBA code help
 

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

VBA code help
 

try

try
ActiveWorkbook.Names.Add Name:="MyRangeName", RefersTo:= _
"=" & activesheet.name & "!B2:" & Cells(LastRow, LastColumn).Address

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

VBA code help
 

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

VBA code help
 

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

VBA code help
 

#1:

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


mario wrote:

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

VBA code help
 

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

VBA code help
 

Your suggestion looks like a valid alternative to Don's, and it includes the
potentially required apostrophes. Don't see how yours could be "wrong too".

Unless of course the OP's question was wrong, which might make mine right
after all <g

FWIW, yet one more way -

ActiveWorkbook.Names.Add _
"MyRangeName2", _
.Range("b2", .Cells(lastrow, lastcol))
End With

Regards,
Peter T

"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

VBA code help
 

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