VBA - Loop through all dates between 2 dates creating new sheets

[Magoosball]

Hello

I have a start date in cell C7 and an end date in cell C9. I'm trying to write a macro that will create a new sheet for each day between the 2 date values and name the sheet the weekday of that date.

Example:
Cell C7 = 3/29/2019
Cell C9 = 4/2/2019
This should create 5 new sheets named "Friday", "Saturday", "Sunday", "Monday", "Tuesday"

Example 2:
Cell C7 = 4/2/2019
Cell C9 = 4/2/2019
This Should create 1 new sheet named "Tuesday"

Condition: If the date range between the start and end date is greater than 7 then create a message-box stating the start date can't span more than 1 week from the end date.


Thank you!!

 

[mumps]

Try:

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim x As Long
    If Range("C9").Value - Range("C7").Value > 7 Then
        MsgBox ("The start date can't span more than 1 week from the end date.")
        Exit Sub
    End If
    For x = Range("C7").Value To Range("C9").Value - 1
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(CDate(x), "dddd")
    Next x
End Sub

 

[Magoosball]

Thank you for the prompt response. I was pretty dang close to getting it on my own ! I was able to get this working. I had to delete the "-1" from the end of "For x = Range("C7").Value To Range("C9").Value so it encompassed the end date."

Thanks again!

 

[mumps]

You are very welcome.