dates on worksheets
 

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.
--
Ann

dates on worksheets
 

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

--
Jacob (MVP - Excel)


"Ann" wrote:

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.
--
Ann

dates on worksheets
 

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


--
Best Regards,

Luke M
"Ann" wrote in message
...
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.
--
Ann

dates on worksheets
 

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).

--
Best Regards,

Luke M
"Luke M" wrote in message
...
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


--
Best Regards,

Luke M
"Ann" wrote in message
...
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.
--
Ann

dates on worksheets
 

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

On Thu, 29 Apr 2010 07:59:02 -0700, Ann
wrote:

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.

dates on worksheets
 

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

On Thu, 29 Apr 2010 08:20:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Thu, 29 Apr 2010 07:59:02 -0700, Ann
wrote:

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.