Autofill dates across worksheets

[Charlene]

I have a workbook with 31 sheets. I want to put the first date of the month in Sheet 1 cell A1 and fill the rest of the month's dates in A1 of the other sheets. I've just switched from Lotus to Excel. I could do it in Lotus, but haven't figured out how to do it in Excel.

 

[Frank Kabel]

Hi Charlene
one way:
- enter your date on the first sheet
- on the second sheet enter the following in cell A1
='sheet1'!A1+1

on the third sheet enter
='sheet1'!A2+1

etc.

 

[A.W.J. Ales]

Charlene,

Put this macro in a VBA module of your file ( Start VB editor with <ALT> +
F11 ; from the menu Insert / module and paste)
Then click on the X (top right) to get back to the "normal" Excel
environment.
From there run the macro ( <ALT> + F8).

Make sure the first (most to the left) sheet contains the date AND that the
cell in the sheets may be overwritten.


Sub FillDates()
Dim Sh As Worksheet
Dim I As Integer
Dim ShName As String

For I = 2 To Worksheets.Count
ShName = Worksheets(I - 1).Name
Worksheets(I).Range("A1").Formula = "=" & ShName & "!A1+1"
Next
End Sub

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

I have a workbook with 31 sheets. I want to put the first date of the

month in Sheet 1 cell A1 and fill the rest of the month's dates in A1 of the
other sheets. I've just switched from Lotus to Excel. I could do it in
Lotus, but haven't figured out how to do it in Excel.

 

[A.W.J. Ales]

Hi Frank,

If think that if Charlene wants to follow this approach the formula on the
third sheet should be ='sheet2'!A1+1 etc.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Gord Dibben]

Charlene

You could use a macro.

Sub Date_Increment()
'''increment a date in A1 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2004, 1, 1)
'''adjust the above for month
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 Excel MVP