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

George1998

New Member
Joined
Feb 28, 2019
Messages
2
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? :confused:
Thanks for the help!
 

Some videos you may like

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
Joined
Dec 18, 2012
Messages
632
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,788
Messages
5,524,879
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top