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

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

regards
FSt1

"Les" wrote:

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

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" wrote:

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

regards
FSt1

"Les" wrote:

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

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

Regards
FSt1

"Barb Reinhardt" wrote:

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" wrote:

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

regards
FSt1

"Les" wrote:

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

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

Regards
FSt1

"FSt1" wrote:

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

Regards
FSt1

"Barb Reinhardt" wrote:

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" wrote:

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

regards
FSt1

"Les" wrote:

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

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

"Les" wrote in message
...
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

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

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

"Barb Reinhardt" wrote:

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" wrote:

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

regards
FSt1

"Les" wrote:

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

Well, Les, so far you are winning this year's prize for the least helpful
description of a problem. "is not working " is not very specific. What
value did the formula return? What value did you expect it to return? Are
you sure that you've got my formula exactly as I wrote it? Perhaps you
should copy the formula from your formula bar and paste it back here so that
we can see it?

The time when the CELL function doesn't work (returning the #VALUE! error)
is when you haven't saved the workbook, but you say that the workbook is
named test so it sounds as if the workbook has been saved.
Another reason for getting a #VALUE! error from the cell function would be
if the info_type argument of the function is invalid, for example if you had
mistyped the word "filename".

Have you looked at the results for the CELL function from Excel help to see
if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

Hi David,

I apologize for not being as specific as I could have been. Whats
interesting is that although I initially saved the workbook as test it didnt
show the file name when I cut and pasted your code. When I just now opened
it to see about providing you better info as to what I was getting it worked
FINE. It seems that although I saved it initially when I tried it I may have
had to close down Excel and started it up again for that change to take
place!! It works just as you told me it would. Thanks so much for your help
and all of you who helped. I will be more specific in the future when
discussing a problem.

Happy New Year,

Les

"David Biddulph" wrote:

Well, Les, so far you are winning this year's prize for the least helpful
description of a problem. "is not working " is not very specific. What
value did the formula return? What value did you expect it to return? Are
you sure that you've got my formula exactly as I wrote it? Perhaps you
should copy the formula from your formula bar and paste it back here so that
we can see it?

The time when the CELL function doesn't work (returning the #VALUE! error)
is when you haven't saved the workbook, but you say that the workbook is
named test so it sounds as if the workbook has been saved.
Another reason for getting a #VALUE! error from the cell function would be
if the info_type argument of the function is invalid, for example if you had
mistyped the word "filename".

Have you looked at the results for the CELL function from Excel help to see
if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

Glad it worked.
--
David Biddulph

Les wrote:
Hi David,

I apologize for not being as specific as I could have been. Whats
interesting is that although I initially saved the workbook as test
it didnt show the file name when I cut and pasted your code. When I
just now opened it to see about providing you better info as to what
I was getting it worked FINE. It seems that although I saved it
initially when I tried it I may have had to close down Excel and
started it up again for that change to take place!! It works just as
you told me it would. Thanks so much for your help and all of you
who helped. I will be more specific in the future when discussing a
problem.

Happy New Year,

Les

"David Biddulph" wrote:

Well, Les, so far you are winning this year's prize for the least
helpful description of a problem. "is not working " is not very
specific. What value did the formula return? What value did you
expect it to return? Are you sure that you've got my formula
exactly as I wrote it? Perhaps you should copy the formula from
your formula bar and paste it back here so that we can see it?

The time when the CELL function doesn't work (returning the #VALUE!
error) is when you haven't saved the workbook, but you say that the
workbook is named test so it sounds as if the workbook has been
saved.
Another reason for getting a #VALUE! error from the cell function
would be if the info_type argument of the function is invalid, for
example if you had mistyped the word "filename".

Have you looked at the results for the CELL function from Excel help
to see if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

That formula will only work if the workbook has been saved.



Les wrote:

Hi David,

I apologize for not being as specific as I could have been. Whats
interesting is that although I initially saved the workbook as test it didnt
show the file name when I cut and pasted your code. When I just now opened
it to see about providing you better info as to what I was getting it worked
FINE. It seems that although I saved it initially when I tried it I may have
had to close down Excel and started it up again for that change to take
place!! It works just as you told me it would. Thanks so much for your help
and all of you who helped. I will be more specific in the future when
discussing a problem.

Happy New Year,

Les

"David Biddulph" wrote:

Well, Les, so far you are winning this year's prize for the least helpful
description of a problem. "is not working " is not very specific. What
value did the formula return? What value did you expect it to return? Are
you sure that you've got my formula exactly as I wrote it? Perhaps you
should copy the formula from your formula bar and paste it back here so that
we can see it?

The time when the CELL function doesn't work (returning the #VALUE! error)
is when you haven't saved the workbook, but you say that the workbook is
named test so it sounds as if the workbook has been saved.
Another reason for getting a #VALUE! error from the cell function would be
if the info_type argument of the function is invalid, for example if you had
mistyped the word "filename".

Have you looked at the results for the CELL function from Excel help to see
if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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




--

Dave Peterson

Dave Peterson wrote:
That formula will only work if the workbook has been saved.

Yes, hence my contribution (to which Les was replying):
"
The time when the CELL function doesn't work (returning the #VALUE!
error)
is when you haven't saved the workbook, but you say that the workbook is
named test so it sounds as if the workbook has been saved.
"
--
David Biddulph


Les wrote:

Hi David,

I apologize for not being as specific as I could have been. Whats
interesting is that although I initially saved the workbook as test
it didnt show the file name when I cut and pasted your code. When I
just now opened it to see about providing you better info as to what
I was getting it worked FINE. It seems that although I saved it
initially when I tried it I may have had to close down Excel and
started it up again for that change to take place!! It works just
as you told me it would. Thanks so much for your help and all of
you who helped. I will be more specific in the future when
discussing a problem.

Happy New Year,

Les

"David Biddulph" wrote:

Well, Les, so far you are winning this year's prize for the least
helpful description of a problem. "is not working " is not very
specific. What value did the formula return? What value did you
expect it to return? Are you sure that you've got my formula
exactly as I wrote it? Perhaps you should copy the formula from
your formula bar and paste it back here so that we can see it?

The time when the CELL function doesn't work (returning the #VALUE!
error) is when you haven't saved the workbook, but you say that the
workbook is named test so it sounds as if the workbook has been
saved.
Another reason for getting a #VALUE! error from the cell function
would be if the info_type argument of the function is invalid, for
example if you had mistyped the word "filename".

Have you looked at the results for the CELL function from Excel
help to see if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

It sure reads like Les missed that portion of your post.

David Biddulph wrote:

Dave Peterson wrote:
That formula will only work if the workbook has been saved.

Yes, hence my contribution (to which Les was replying):
"
The time when the CELL function doesn't work (returning the #VALUE!
error)
is when you haven't saved the workbook, but you say that the workbook is
named test so it sounds as if the workbook has been saved.
"
--
David Biddulph

Les wrote:

Hi David,

I apologize for not being as specific as I could have been. Whats
interesting is that although I initially saved the workbook as test
it didnt show the file name when I cut and pasted your code. When I
just now opened it to see about providing you better info as to what
I was getting it worked FINE. It seems that although I saved it
initially when I tried it I may have had to close down Excel and
started it up again for that change to take place!! It works just
as you told me it would. Thanks so much for your help and all of
you who helped. I will be more specific in the future when
discussing a problem.

Happy New Year,

Les

"David Biddulph" wrote:

Well, Les, so far you are winning this year's prize for the least
helpful description of a problem. "is not working " is not very
specific. What value did the formula return? What value did you
expect it to return? Are you sure that you've got my formula
exactly as I wrote it? Perhaps you should copy the formula from
your formula bar and paste it back here so that we can see it?

The time when the CELL function doesn't work (returning the #VALUE!
error) is when you haven't saved the workbook, but you say that the
workbook is named test so it sounds as if the workbook has been
saved.
Another reason for getting a #VALUE! error from the cell function
would be if the info_type argument of the function is invalid, for
example if you had mistyped the word "filename".

Have you looked at the results for the CELL function from Excel
help to see if that helps?
If you are convinced that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
doesn't return the sheet name for you, what does the formula
=CELL("filename",A1)
return for you?
--
David Biddulph

Les wrote:
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

"David Biddulph" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
A non-VBA way of grabbing the sheet name is
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
--
David Biddulph

"Les" wrote in message
...
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

--

Dave Peterson