VBA to autofill date range in increments of 7 by referencing two cells in different sheets

[datauser]

Hello!

I am looking to create a vba that will auto fill a date range in increments of 7 by referencing two cells in different sheets. The start day must be on a Monday and will be entered in manually on the final sheet by the end user . I need the range to fill in horizontally until the end date is reached that is found on another sheet. This is my first experience with macros and any help would be appreciated!

 

[Micron]

Not much to go on there, so I can only generalize. By that I mean no sheet names, no starting cell address, or whatever else I can't envision at the moment.

You can validate "Monday" with weekdayname(day(date)) where date would be a reference to the cell with the date that as input. I think that cell would need to be formatted as a date. This way, it won't matter what your regional settings are for the first day of the week.

DateDiff will give you the difference between 2 dates. Again, the end date would be a cell reference. Datediff("w",#09/02/2024#,#11/22/2024#) returns 11; assign that to a variable (e.g. i). Thus i = 11 with those dates.

I interpret your post to mean that the date in the cell to the right of the start date will be 7 days later, next is 7 days after that and so on.
So in a code loop count from 1 to i, but somewhere before the loop make x = 7. Inside the loop reference the cell with the start date and Offset(0,i)to go 1 to the right and make that cell the start date +x. Add 7 to x and repeat the loop.

Can you work with that?

 

[datauser]

Not much to go on there, so I can only generalize. By that I mean no sheet names, no starting cell address, or whatever else I can't envision at the moment.

You can validate "Monday" with weekdayname(day(date)) where date would be a reference to the cell with the date that as input. I think that cell would need to be formatted as a date. This way, it won't matter what your regional settings are for the first day of the week.

DateDiff will give you the difference between 2 dates. Again, the end date would be a cell reference. Datediff("w",#09/02/2024#,#11/22/2024#) returns 11; assign that to a variable (e.g. i). Thus i = 11 with those dates.

I interpret your post to mean that the date in the cell to the right of the start date will be 7 days later, next is 7 days after that and so on.
So in a code loop count from 1 to i, but somewhere before the loop make x = 7. Inside the loop reference the cell with the start date and Offset(0,i)to go 1 to the right and make that cell the start date +x. Add 7 to x and repeat the loop.

Can you work with that?

I can't attach my workbook, but here are two screenshots of my file. The End Date is coming from Sheet "Query2".

I did insert a data validation on cell C3. The formula that I have in D3 is =C3+7, I need this formula to run until it reached the end date of 11/25/2024, which this date will be different depending on each project.

Thanks!

 

[Micron]

So this only ever involves one row (row 2 on one 1 sheet, row 3 on the other)?
You have to imagine that anyone who helps you knows little or nothing about what you have to work with, or what you will be working with.

 

[Micron]

Assuming yes to my question, try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim beginDate As Date, endDate As Date
Dim i As Integer, x As Integer

If Not Intersect(Target, Range("$C$3")) Is Nothing Then
    beginDate = Target
    endDate = Sheets("Query2").Range("G2")
    If Not IsDate(endDate) Or Not IsDate(beginDate) Then
        MsgBox "Begin or end date is not valid"
        Exit Sub
    End If
    If WeekdayName(Weekday(beginDate, vbSunday)) <> "Monday" Then
        MsgBox beginDate & " is not a valid date for a Monday."
        Exit Sub
    End If
    x = 7
    For i = 1 To DateDiff("w", beginDate, endDate, vbSunday)
        Target.Offset(0, i) = Target + x
        x = x + 7
    Next
End If

End Sub

 

[datauser]

Thank you so much for your help! This worked!

 

[Micron]

Glad I could help. If you have a solution, please mark this a solved. That moves the solution to the top so that others don't have to read the whole thread to figure that out and takes your thread off the list of threads still needing to be solved.