How can I use the value on a tab in a formula?

[Guest]

I am trying to create a workbook with 52 sheets, one for each week in a
year. There needs to be a column for each day of the week on each sheet. I
want the Friday's date to appear as the tab's value on each sheet. And
finally, I want to be able to use &[Tab] in the heading. Ideas?

 

[Don Guillett]

Not quite sure what you want but. Make a sheet with the formatting,etc as
desired and name it 0105 for the first Friday of this year. Then run this
macro

Sub makesheets()
ff = DateValue("Jan 5, 2007")
For i = 2 To 4
Sheets("0105").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(ff + (7 * i) - 7, "mmdd")
Next i
End Sub

 

[Guest]

If I understand correctly this should give you want you want and you can
perform calculations with the result, if anybody knows a cleaner method I'm
all ears!
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

 

[PCLIVE]

Keep in mind for this formula to work, the workbook will need to have
already been saved. Otherwise, it returns #VALUE.

If I understand correctly this should give you want you want and you can
perform calculations with the result, if anybody knows a cleaner method
I'm
all ears!
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.

I am trying to create a workbook with 52 sheets, one for each week in a
year. There needs to be a column for each day of the week on each sheet.
I
want the Friday's date to appear as the tab's value on each sheet. And
finally, I want to be able to use &[Tab] in the heading. Ideas?

 

[Gord Dibben]

Boyd

You want the sheet tabs' names to be Friday's date from each sheet?

And each sheet has the next week after the previous sheet?

A1:G1 on each sheet is Sunday through Saturday?

Start with a new workbook with one sheet in it.

In A1 enter 1/1/2007

In B1 enter =A1+1 copy that across to G1

Run this macro to copy sheet1 51 times.

Sub SheetCopy()
Dim i As Long
On Error GoTo endit
Application.ScreenUpdating = False
For i = 1 To 51
ActiveSheet.Copy After:=ActiveSheet
Next i
Application.ScreenUpdating = True
endit:
End Sub

Add this UDF to your module where you placed the SheetCopy macro above.

Function PrevSheet(rg As Range)
Application.Volatile
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

Select sheet2 through sheet52 and in A1 of sheet2 enter this =PrevSheet(A1)+7

Ungroup the sheets.

Run this macro to re-name all the sheets to F1 which is Friday's date from each
sheet.

Sub Sheetname_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("F1").Value
Next
Application.ScreenUpdating = True
End Sub

All the above could be combined into one routine if you chose to make the
alterations to the code.

The &[Tab] is found under headers and footers.

Group the sheets and add the &[Tab] to active sheet and will be done to all.


Gord Dibben MS Excel MVP


On Mon, 7 May 2007 05:32:01 -0700, Boyd Parks <Boyd