Autofill dates across worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Charlene said:
I have a workbook with 31 sheets. I want to put the first date of the
Click to expand...
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.
 
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 *
 
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
 
Back
Top