How to get sheet name in formula or macro

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Guest]

that is useful. How can I do the same using formula instead of VB?

 

[Gord Dibben]

prashant

Copy/paste this User Defined Function to a general module in your workbook.

Function PrevSheet(rg As Range)
N = Application.Caller.Parent.Index
If N = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(N - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(N - 1).Range(rg.Address).Value
End If
End Function

'Enter =PrevSheet(B2) on sheet2 and you'll get B2 data from sheet1.

OR call the Function in your code.

Gord Dibben Excel MVP