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

#### George1998

##### New Member
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 1st January but not exceeding past
The current date.
How would I go about doing this?
Thanks for the help!

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Perpa

##### Well-known Member
George,
Welcome to the Forumn...
If each month's worksheets are set up similar to this:

Excel 2007 32 bit
A
B
C
D
1
January
2019​
2
3
4
5
6
7
8
9
10
11
Friday
1​
35​
12
Saturday
2​
35​
13
Sunday
3​
35​
14
Monday
4​
35​
15
Tuesday
5​
35​
16
Wednesday
6​
35​
17
Thursday
7​
35​
 Sheet: Sheet1

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

Code:
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

Last edited:

#### George1998

##### New Member
That's for the answer Perpa .
It's well appreciated...

#### Perpa

##### Well-known Member
That's for the answer Perpa .
It's well appreciated...

George,
You are welcome.
Perpa

Replies
0
Views
75
Replies
0
Views
218
Replies
0
Views
34
Replies
11
Views
367
Legacy 456129
L
Replies
1
Views
52