Using Sheet instead of tab name

[Microsoft Communities]

Actually I have two topics.
#1. Is there a way to ref. in a formula the sheet name rather than the tab
name.
Example: sheet4 has a tab name of 08-01-2009 and I want the value from cell
a5, but the tab name changes from time to time. So I am looking to get the
information from "Sheet4" (a5). not "08-01-2009"(a5)
And
#2. Is a hard one. I would like to copy a sheet to up to 31 sheets (the
number of days in the month) starting again with sheet4 and the sheet name
may not be sheet4 every time but it would be starting at the next sheet.

Hope I explained properly what I am trying to do.

Thanks for all your help.

 

[Dave Peterson]

Each sheet in a workbook has the name property that you see on the tab and a
codename property that you can use in VBA macros. The codename is much more
difficult for the average user to change, but not impossible to be changed.

#1. You'd have to use a macro (a user defined function (UDF) in this case).
But I think you'd have to pass a string to that UDF so that it may not be worth
doing.

If your function were called by:
=myFunc("Sheet4","A4")

Then changes to that worksheet (inserting/deleting of rows/columns) would not be
reflected in this formula. It'd be much like using =indirect() in your
functions.

I think that this would be a bad idea in almost all cases.

#2. You could use the codename in your code that copies the last sheet, but it
seems more natural to just use the last sheet in the workbook:

Dim wks as worksheet
dim iCtr as long
with activeworkbook
set wks = .worksheets(.worksheets.count) 'the last worksheet
for ictr = 1 to 31
wks.copy _
after:=.worksheets(.worksheets.count) 'the "new" last worksheet
next ictr
end with

(untested, uncompiled)