How to Auto Fill a calendar up to today’s date Calendar ?

[George1998]

Hello Guys,
I am looking for help with a formula,
I have a spreadsheet, it’s a calendar and it is laid
Out like this:
January is on sheet 1,
February is on sheet 2
Up to December on sheet 12
The Days are in a column B11 to B41
The Dates are in a column C11 to C41
It’s like this on all sheets.
I have a number 35 that needs inserting into the calendar
Starting from the 1^st January but not exceeding past
The current date.
How would I go about doing this?
Thanks for the help!

 

[Perpa]

George,
Welcome to the Forumn...
If each month's worksheets are set up similar to this:

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]January[/td][td]

2019​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td]Friday[/td][td]

1​

[/td][td]

35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td]Saturday[/td][td]

2​

[/td][td]

35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td]Sunday[/td][td]

3​

[/td][td]

35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td]Monday[/td][td]

4​

[/td][td]

35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td]Tuesday[/td][td]

5​

[/td][td]

35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td]Wednesday[/td][td]

6​

[/td][td]

35​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][td]Thursday[/td][td]

7​

[/td][td]

35​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]





Then the following macro ought to place '35' in column D until Today's Date is reached:

Sub Test()
Dim ws As Worksheet
Dim wb As Workbook
Dim Crw As Long


Set wb = ActiveWorkbook


    For Each ws In wb.Sheets
           For Crw = 11 To 41
                 If Cells(Crw, "C") <> "" And DateValue(Cells(1, "A") & " " & Cells(Crw, "C") & ", " & Cells(1, "B")) < Date Then Cells(Crw, "D") = 35
          Next Crw
    Next ws
End Sub

Good luck!
Perpa

 

[George1998]

That's for the answer Perpa .
It's well appreciated...

 

[Perpa]

That's for the answer Perpa .
It's well appreciated...

George,
You are welcome.
Perpa