Hi chaps

I want to create a workbook with, say, 52 worksheets, one for each week. I
would like the days of the week, in date format across the top of each work
sheet, but I want the first sheet to be 1/1/08,2/1/08 etc, with the second
worksheet continuing the dates say 8/1/08 to 14/1/08. Can this be done
automatically using a function or am I in macro territory? Ideally the
worksheet tabs would also be named Week 1, Week 2 etc.

Hope you can help

Ello Guvnor. Yes it can be done. Start with the following formula:

=LOWER(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256))

This will give you a cell containing the name of your worksheet, for example
"Week 1"

Use that cell and do a "FIND" command to get the week number, for example "1"

Using that, you can create a formula which will calculate the appropriate
start date, then the other cells can be the previous cell + 1 which will give
you what you need


"Mick B" wrote:

Hi chaps

I want to create a workbook with, say, 52 worksheets, one for each week. I
would like the days of the week, in date format across the top of each work
sheet, but I want the first sheet to be 1/1/08,2/1/08 etc, with the second
worksheet continuing the dates say 8/1/08 to 14/1/08. Can this be done
automatically using a function or am I in macro territory? Ideally the
worksheet tabs would also be named Week 1, Week 2 etc.

Hope you can help

Open a new workbook.

Copy PrevSheet UDF and SheetCopy macro to a General module.

Delete all but two sheets.

Name these Week1 and Week2

In A1 of Week1 enter 1/1/2008

Copy across to G1

In Week2 A1 enter =PrevSheet(A1)+7

Copy across to G1

Run the SheetCopy macro.

Sub SheetCopy()
Dim I As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = 52
For I = 3 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "Week" & I
End With
Next I
Application.ScreenUpdating = True
endit:
End Sub

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


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 08:19:01 -0700, Mick B
wrote:

Hi chaps

I want to create a workbook with, say, 52 worksheets, one for each week. I
would like the days of the week, in date format across the top of each work
sheet, but I want the first sheet to be 1/1/08,2/1/08 etc, with the second
worksheet continuing the dates say 8/1/08 to 14/1/08. Can this be done
automatically using a function or am I in macro territory? Ideally the
worksheet tabs would also be named Week 1, Week 2 etc.

Hope you can help

Sorry Gord, but you will have to explain Copy PrevSheet UDF and SheetCopy
macro to me please!

Thanks

"Gord Dibben" wrote:

Open a new workbook.

Copy PrevSheet UDF and SheetCopy macro to a General module.

Delete all but two sheets.

Name these Week1 and Week2

In A1 of Week1 enter 1/1/2008

Copy across to G1

In Week2 A1 enter =PrevSheet(A1)+7

Copy across to G1

Run the SheetCopy macro.

Sub SheetCopy()
Dim I As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = 52
For I = 3 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "Week" & I
End With
Next I
Application.ScreenUpdating = True
endit:
End Sub

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


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 08:19:01 -0700, Mick B
wrote:

Hi chaps

I want to create a workbook with, say, 52 worksheets, one for each week. I
would like the days of the week, in date format across the top of each work
sheet, but I want the first sheet to be 1/1/08,2/1/08 etc, with the second
worksheet continuing the dates say 8/1/08 to 14/1/08. Can this be done
automatically using a function or am I in macro territory? Ideally the
worksheet tabs would also be named Week 1, Week 2 etc.

Hope you can help

I posted a user defined function(UDF) and a macro named SheetCopy for you to
copy into your workbook.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the PrevSheet Function and the Macro in
there. Save the workbook and hit ALT + q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You don't have to "run" the PrevSheet, just enter it in the cells as described
in first post.


Gord

On Thu, 22 May 2008 07:50:01 -0700, Mick B
wrote:

Sorry Gord, but you will have to explain Copy PrevSheet UDF and SheetCopy
macro to me please!

Thanks

"Gord Dibben" wrote:

Open a new workbook.

Copy PrevSheet UDF and SheetCopy macro to a General module.

Delete all but two sheets.

Name these Week1 and Week2

In A1 of Week1 enter 1/1/2008

Copy across to G1

In Week2 A1 enter =PrevSheet(A1)+7

Copy across to G1

Run the SheetCopy macro.

Sub SheetCopy()
Dim I As Long
On Error GoTo endit
Application.ScreenUpdating = False
shts = 52
For I = 3 To shts
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
.Name = "Week" & I
End With
Next I
Application.ScreenUpdating = True
endit:
End Sub

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


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 08:19:01 -0700, Mick B
wrote:

Hi chaps

I want to create a workbook with, say, 52 worksheets, one for each week. I
would like the days of the week, in date format across the top of each work
sheet, but I want the first sheet to be 1/1/08,2/1/08 etc, with the second
worksheet continuing the dates say 8/1/08 to 14/1/08. Can this be done
automatically using a function or am I in macro territory? Ideally the
worksheet tabs would also be named Week 1, Week 2 etc.

Hope you can help