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