Macro for creating a series of dates?

[RedOctoberKnight]

Hello,

I'm fairly new to using macros. I was wondering if it were possible to create a macro that automatically inserts a date onto a each sheet?

For example, I have staffing tool that I created where each day is on it's own separate sheet. I currently have "settings" sheet where put in the starting date of the schedule. So if I input 10/3/18 in cell C2 on the "settings" sheet, then on sheet2 it would show 10/3/18 in cell E2. Sheet3 would show 10/4/18 in cell E2. Sheet4 would show 10/5/18 in cell E2 an so on.

Any input would be much appreciated.

Thanks.

 

[BarryL]

Does it have to be a macro or could you not just use formulas?

 

[Special-K99]

Why don't you just put a formula on each sheet (depends how many sheets you have I guess) ?

in Sheet2!E2
=Settings!C2+(SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"Sheet","")-2)

Same formula on other sheets in E2

NOTE: The file will need to be saved first before the CELL formula works. Once it's a saved file you should be ok

 

[RedOctoberKnight]

I currently use a formula. The problem that I'm having is its taking quite a long time to recalculate everything when I change the date in the Settings sheet. I'm sure it has something to do with the formulas I used in other areas. I'm probably using them inefficiently as I am self taught user

 

[BarryL]

what formula are you using?

 

[My Aswer Is This]

Try this:
Run this script from the settings sheet which I assume is sheet 1
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab Named settings which should be sheet1
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
This script will run when you enter a Date in Range("C3") on the settings sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
'Modified  10/4/2018  5:25:08 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim d As Date
ans = Cells(2, 3).Value
    For i = 2 To Sheets.Count
        d = DateAdd("d", i - 2, ans)
        
        With Sheets(i)
        .Cells(2, "E").Value = d
        End With
    Next
End If
Application.ScreenUpdating = False
End Sub