Excel sheet names

[Les]

Hi,

Is there an easy way fro me to grab the name of the active sheet I am in. ie
if I am in sheet9 I want to be able to use the name sheet9 in code etc?
Thanks in advane,

Les

 

[FSt1]

hi
try this for starts....
sub findname()
Dim n As String
n = ActiveSheet.Name
MsgBox n
end sub

regards
FSt1

 

[Barb Reinhardt]

I'd probably modify your code like this

sub findname()
Dim aWS as Worksheet
Set aWS = ActiveSheet
MsgBox aWS.name
end sub

You can use this like this

aWS.Range("A1").value = "myValue"
aWS.Cells(1,1).formulaR1C1 = "=1"


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[FSt1]

hi,
yes, a lot of ways to do it. i just grabed the first thing that came to mind.
lazy????

Regards
FSt1

 

[FSt1]

hi
after thinking about it, my way took less typing and works.
yeah......lazy.

Regards
FSt1

 

[David Biddulph]

A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

 

[David Biddulph]

Sorry, that should have been
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
--
David Biddulph

A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

Hi,

Is there an easy way fro me to grab the name of the active sheet I am in.
ie
if I am in sheet9 I want to be able to use the name sheet9 in code etc?
Thanks in advane,

Les

 

[Les]

Hi David,

I dont kow but your cod is not working for some reason. What have is a
workbok called test that has 3default sheets (sheet1, sheet2, sheet3). Now
what I want to be able to do is the following. If I am in sheet1 I want to
list the sheet name in lets say cell b2. I I go to sheet2 I will have your
cde there 2 but this time I want cell b2 on sheet2 to say sheet2 etc.
Basically depending on what sheet I am in, all sheets will have your code
there to start with, so that I can use the sheet name in some of my
spreadsheet.

Hope I made myself clear, sorry for the confusion.

Thx Les

Sorry, that should have been
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
--
David Biddulph

A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

Hi,

Is there an easy way fro me to grab the name of the active sheet I am in.
ie
if I am in sheet9 I want to be able to use the name sheet9 in code etc?
Thanks in advane,

Les

 

[Les]

Hi Barb,

Will your code tell me the name of the sheet I am in. Basically want to
place th same code on all 3 sheets and then when I work on sheet 2 I can use
the name of the sheet I am in with some of my preadhseet data etc.

Thx so much and a Happy New ear.

Les