VBA code help

[mario]

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

 

[Don Guillett]

try

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

 

[Peter T]

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

 

[Peter T]

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

 

[Dave Peterson]

#1:

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

 

[Dave Peterson]

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]

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

 

[Don Guillett]

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"