Sequential dates across worksheets

M

Mick B

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
 
D

dhstein

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
 
G

Gord Dibben

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
 
M

Mick B

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

Thanks
 
G

Gord Dibben

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 Insert>Module. 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 Tool>Macro>Macros.

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


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top