How to get sheet name in formula or macro
 

Hi!

I need to write a macro/formula that uses the sheet name. I do not want to
type it in but want it as some type of function like sheetname(). Then using
that I like to manipulate the output of sheetname(). Can I do this? E.g., I
have name each of my sheet as date of the month (like"1", "2", etc.). I like
to get data from the previous day (worksheet). How can I do this without
manually typing it each time?

thank you,

prashant
--
Excel is excellent

How to get sheet name in formula or macro
 

set sh = ActiveSheet
sName = sh.Name
sName = cStr(clng(sName)-1)
if sName = "0" then
msgbox "No previous sheet"
exit sub
end if
set sh1 = sheets(sName)
msgbox sh1.Name


another is

set sh1 = activesheet.previous
msgbox sh1.Name

this assume the sheets are sequential in the tab order.



--
Regards,
Tom Ogilvy

"psp" wrote in message
...
Hi!

I need to write a macro/formula that uses the sheet name. I do not want to
type it in but want it as some type of function like sheetname(). Then
using
that I like to manipulate the output of sheetname(). Can I do this? E.g.,
I
have name each of my sheet as date of the month (like"1", "2", etc.). I
like
to get data from the previous day (worksheet). How can I do this without
manually typing it each time?

thank you,

prashant
--
Excel is excellent

How to get sheet name in formula or macro
 

hi,
you didn't say what ranges on the previous sheet you need
to copy so....
sniplet
activesheet.previous.select
'your copy code here
seletion.copy
activesheet.next.select
'paste it somewhere.
this code sniplet will go to the previous sheet from
active sheet, copy some data, return to the the first
sheet you were on and paste the data somewhere.
good luck

-----Original Message-----
Hi!

I need to write a macro/formula that uses the sheet name.
I do not want to
type it in but want it as some type of function like
sheetname(). Then using
that I like to manipulate the output of sheetname(). Can
I do this? E.g., I
have name each of my sheet as date of the month
(like"1", "2", etc.). I like
to get data from the previous day (worksheet). How can I
do this without
manually typing it each time?

thank you,

prashant
--
Excel is excellent
.