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
wrote:

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?