dates on worksheets

[Ann]

I want to type the date and day (05/01/2010 Sat) on my 31 sheets.
How can I type in the first day of the month and have each successive sheet
advance one day without typing it on each sheet individually?
Thanks so much.

 

[Jacob Skaria]

Try the below

--Make sure you try this in a saved workbook
--Name the sheets as 1,2,3,4...upto 31
--Enter the first date say 05/01/2010 in Sheet '1' cell A1
--Select sheets 1 to 31 and select cell A1 and format it to the date format
you need.
--Select sheets 2 to 31 and enter the below formula in cell A1
PS: You can select or group the sheets by holding the control key and
selecting the sheets..



=INDIRECT("'" & REPLACE(CELL("Filename",A1),1,FIND("]",
CELL("filename",A1)),"")-1 & "'!A1")+1

 

[Luke M]

You could use this short macro to do all the work. Note that you can't
actually name a sheet "5/1/2010 Sat" as slash marks are not allowed in sheet
names.


Sub DateSheets()
'Starting Value
x = DateValue("5/1/2010")
i = 1
'First, clear out the old names
For Each ws In ThisWorkbook.Worksheets
ws.Name = "zzzz" & i
i = i + 1
Next

'Now, give it the real name
For Each ws In ThisWorkbook.Worksheets
'Note that you can't use a / in sheet names!
ws.Name = Format(x, "dd.mm.yyyy ddd")
x = x + 1
Next ws

End Sub

 

[Luke M]

I realize now that Jacob and I interpretted your request differntly. I was
thinking you wanted to name the sheets, Jacob's solution is for putting the
date IN the sheet (which, in rereading, looks to be what you are really
wanting).

 

[Gord Dibben]

Sub Date_Increment()
''increment a date in A1 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2010, 5, 1)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm-dd-yyyy"
End With
Next iCtr
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Since you have 31 sheets you may want to be doing other computations across
sheets.

This UDF can help with that.

Copy/paste this UDF to a general module in your workbook.

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

Example of usage...................

Say you have 31 sheets, sheet1 through shee31...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9

You could add your dates in the same manner.

Sheet1 A1 = 5/1/2010

Select sheets2 to 31 and in a cell enter

=PrevSheet(A1) + 1


Gord Dibben MS Excel MVP