automatically updating calendar

[pdub125]

I have data in sheet 1. There are dates in B2:H54. Column B is always Mondays and C is Tuesdays and it continues until Sunday in column H. Then in I2:O54 is the data that corresponds to my dates. So column I is always Mondays and column O is always Sunday. The data on sheet 1 will be updated weekly on Mondays. So this week B2 is 11/12/18 but next week it will be 11/19/18.

Is there a way I can get that information put in a calendar in excel so I can print as needed?

I thought just creating a new sheet that looked like a calendar would be fine but I was told they would rather it be on a calendar. The problem I am having is making the calendar auto update to the correct month with the date in the correct location. Then having the data from sheet 1 I2:O54 be on the correct date.

Sorry if this doesn't make any sense.

 

[pbornemeier]

I cannot completely understand your requirements.

You can use:
=INT(NOW()))
in a cell to get the current date and
=MONTH(NOW())
in a cell to get the current month

This site has several Excel, formula based, calendars by John Walkenbach that you could adapt to your needs:
http://spreadsheetpage.com/index.php/file/yearly_calendar_with_holidays/

 

[pdub125]

Someone creates a spreadsheet every Monday. (always named the same and in the same location) I created a different spreadsheet that is link to theirs. (

Their spreadsheet isn't user friendly.)​

_{^{<strike>
</strike>}}​

My spreadsheet-
Starting on row 2, each row has a week of dates that is followed by the data that is referenced to those dates.
So
Column B is always a Monday.
Column C is always Tuesday.
This continues until column H, which is Sunday.
Column I is the data for column B.
Column J is the data for column C.
This continues until column O.

B2 will always be the Monday of the week the file was created.

How can I create a calendar that will automatically update? I just want them to overwrite last weeks file with this weeks file. Then open that file and my file to update.

Does this help?

 

[pbornemeier]

This sub will create a calendar on another page in the workbook.
Be sure to change the "Sheet1" reference in the first Const row to reflect the name of the sheet that holds the date in B2

Option Explicit
'https://www.mrexcel.com/forum/excel-questions/1077672-automatically-updating-calendar.html

Sub ChangingCalendar()
    'Uses formulas part of the calendar by John Walkenbach
    'http://spreadsheetpage.com/index.php/file/yearly_calendar_with_holidays/
    
    'Change the Const line to refer to the worksheet in this workbook that
    '  holds the date of interest in cell B2
    Const sDataWksName As String = "Sheet1"
    
    'Next Const line holds the name of the sheet in this workbook that
    '  will be created to hold the calendar
    Const sWorksheet As String = "Two Month Calendar"
    
    'Next Const line contains the text value of the range of the upper left cell of the calendar
    Const sULCell As String = "B2"
    
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String

    'Delete & Create the Calendar Page
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(sWorksheet).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sWorksheet 'After last

    With Worksheets(sWorksheet)
        'Basic Format Calendar Page
         Range(.Range(sULCell), .Range(sULCell).Offset(0, 6)).MergeCells = True
         Range(.Range(sULCell).Offset(0, 2), .Range(sULCell).Offset(0, 8)).MergeCells = True
        .Range(sULCell).NumberFormat = "mmmm yyyy"
        .Range(sULCell).Offset(0, 2).NumberFormat = "mmmm yyyy"
        .Range("B3:H3,J3:P3").NumberFormat = "ddd"
        .Range("B4:H9,J4:P9").NumberFormat = "0"
        With .Range("B2:P9")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
    
        'Add Formulas to Calendar Page
        .Range("B2").FormulaR1C1 = _
            "=DATE(YEAR('" & sDataWksName & "'!R2C2),MONTH(" & sDataWksName & "!R2C2),1)"
        .Range("J2").FormulaR1C1 = _
            "=DATE(YEAR('" & sDataWksName & "'!R2C2),MONTH(" & sDataWksName & "!R2C2)+1,1)"
        .Range("B3:H3").FormulaArray = "=TRANSPOSE({1;2;3;4;5;6;7})"
        .Range("J3:P3").FormulaArray = "=TRANSPOSE({1;2;3;4;5;6;7})"
    
        'The next line is too long to enter using VBA
        'Range("A3:G8").FormulaR1C1 = _
            "=IF(MONTH(DATE(YEAR(R1C),MONTH(R1C),1))<>MONTH(DATE(YEAR(R1C),MONTH(R1C),1)-(WEEKDAY(DATE(YEAR(R1C),MONTH(R1C),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),"""",DATE(YEAR(R1C),MONTH(R1C),1)-(WEEKDAY(DATE(YEAR(R1C),MONTH(R1C),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
            
        'Solution found at:
        'http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/
        'Use the FormulaArray property of the Range object to enter array formulas
        'in a cell from VBA. The array formula must be 255 characters or less or you’ll
        'see the ever-helpful “Unable to set the FormulaArray property of the Range class” error.
        'Solution: Split into parseable chunks
            
         theFormulaPart1 = "=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))-" & _
                               "MONTH(DATE(YEAR(B2),MONTH(B2),1)-" & _
                               "(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+" & _
                               "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
                               "X_X_X())"
                              
         theFormulaPart2 = "DATE(YEAR(B2),MONTH(B2),1)-" & _
                           "(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+" & _
                           "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
            
         With .Range("B4:H9")
             .FormulaArray = theFormulaPart1
             .Replace "X_X_X())", theFormulaPart2, LookAt:=xlPart, _
                MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
             .NumberFormat = "d"
        End With
    
         theFormulaPart1 = "=IF(MONTH(DATE(YEAR(J2),MONTH(J2),1))-" & _
                               "MONTH(DATE(YEAR(J2),MONTH(J2),1)-" & _
                               "(WEEKDAY(DATE(YEAR(J2),MONTH(J2),1))-1)+" & _
                               "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
                               "X_X_X())"
                              
         theFormulaPart2 = "DATE(YEAR(J2),MONTH(J2),1)-" & _
                           "(WEEKDAY(DATE(YEAR(J2),MONTH(J2),1))-1)+" & _
                           "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
            
         With .Range("J4:P9")
             .FormulaArray = theFormulaPart1
             .Replace "X_X_X())", theFormulaPart2, LookAt:=xlPart, _
                MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
             .NumberFormat = "d"
        End With
        
        'Tint Active Week Days
        With .Range("B4:P9")
            .Cells.FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=AND(B4>='" & sDataWksName & "'!$B$2,B4<='" & sDataWksName & "'!$B$2+4)"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 16763904
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
        
        'Format Calendar
        With Range("B2:H9,J2:P9")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders
                .LineStyle = xlContinuous
                .Color = rgbLightGrey
                .TintAndShade = 0
                .Weight = xlThin
            End With
        End With
        .Columns("A:Q").ColumnWidth = 4.67

    
    End With
    
End Sub

 

[pdub125]

I will work on this. Thank you very much.

 

[pbornemeier]

You are welcome. Update thread if any questions.