Sheet Name
 

Hi
better not to use a string but a worksheet object reference. something
like

dim wks as worksheet
dim cell as range
set wks = activesheet
set cell = activecell
'...
'your code
'...
wks.activate
cell.select

BUT: In most cases there's no need to switch to the other worksheets
(with select/activate). You can use a worksheet reference like show
above. e.g. if you have a statement like
worksheets("sheet2").activate
Range("A1").select
selection.copy

you can use
with worksheets("sheet2").range("A1")
.copy
end with


--
Regards
Frank Kabel
Frankfurt, Germany


Jonathan Cooper wrote:
I'm trying to learn VBA for Excel. I've got a macro mostly hacked
together. There is one part I can't figure out though.

When the macro runs, I want to capture the active worksheet name and
store it in a variable. Then after the macro finishes, I want to go
back and select that sheet again. Seems like such a simple thing to
do but I can't figure out where the darn worksheet name is stored.

Also, since I'm just learning VBA, if you could tell me how I should
have been able to figure out where the name is stored, that would be
even better.

Sheet Name
 

OK, here's what I've done. I created a one-line function
named "SheetName" that I keep in Personal.xls because I can
never remember the code:
Function SheetName()
SheetName = Range("A1").Parent.Name
End Function
VERY handy function. I reference it in other macros with:
Dim SheetNm as String
SheetNm = SheetName
-----Original Message-----
Hi
better not to use a string but a worksheet object
reference. something
like

dim wks as worksheet
dim cell as range
set wks = activesheet
set cell = activecell
'...
'your code
'...
wks.activate
cell.select

BUT: In most cases there's no need to switch to the other
worksheets
(with select/activate). You can use a worksheet reference
like show
above. e.g. if you have a statement like
worksheets("sheet2").activate
Range("A1").select
selection.copy

you can use
with worksheets("sheet2").range("A1")
.copy
end with


--
Regards
Frank Kabel
Frankfurt, Germany


Jonathan Cooper wrote:
I'm trying to learn VBA for Excel. I've got a macro
mostly hacked
together. There is one part I can't figure out though.

When the macro runs, I want to capture the active
worksheet name and
store it in a variable. Then after the macro finishes,
I want to go
back and select that sheet again. Seems like such a
simple thing to
do but I can't figure out where the darn worksheet name
is stored.

Also, since I'm just learning VBA, if you could tell me
how I should
have been able to figure out where the name is stored,
that would be
even better.

.