Date Series - first date of the Week

[Charlie987]

Hi,
I was hoping to be able to create a date series in excel from the following method:
On a button click, generate a dialog box asking: Enter month and year (but am thinking it may be easiest to just ask for the first of the month eg 1/12/20).
from that,
Starting from E1 of a dynamically created workbook that will hopefully be created on the same click, a series of dates that determine the date of each Monday in that month listed across the row. (where each date is stated twice) (Also if was possible to indicate how I would alter the code to change to only one listing of the date that would be fantastic!)
eg.
Enter Date: 1/12/20
output:
7/12/20 | 7/12/20 | 14/12/20 | 14/12/20 | 21/12/20 | 21/12/20 | 28/12/20 | 28/12/20

I'm not sure how big of a task this is so if not too much trouble was hoping anyone might have some ideas on how to implement
Thank you!

 

[Charlie987]

As I have started using the code above, I have realised that if I enter a month that begins on a monday (first monday is the 1st), it doesn't start from the 1st it skips it and starts at the 8th. Was wondering if anyone knew a way around that?
Thanks
Jenna

 

[mumps]

Try:

Sub Charlie2()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, LastRow As Long, rng As Range, response As String, d As Date, intDay As Long
    Set srcWS = ThisWorkbook.Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    response = InputBox("Enter the date in the format: yyyy/mm/01", "1st of the month")
    If Right(response, 2) <> "01" Then
        MsgBox ("Please enter the date in the format: 'yyyy/mm/01' with '01' as the day.")
        Exit Sub
    End If
    d = response
    intDay = 0
    Workbooks.Add
    Do
        If Format(d + intDay, "ddd") = "Mon" Then
            srcWS.UsedRange.Offset(1).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1)
            Cells(Rows.Count, "E").End(xlUp).Offset(1).Resize(LastRow - 1) = DateSerial(Year(d), Month(d), Format(d + intDay, "d"))
        End If
        intDay = intDay + 1
    Loop Until Format(d + intDay, "mmm") <> Format(d + intDay + 1, "mmm")
    Application.ScreenUpdating = True
End Sub

 

[Charlie987]

Great! Thank you so much!

 

[mumps]

My pleasure.

 

[Charlie987]

So, when I tried to implement it, I realised that because I am calling the script in one workbook but it is being applied to a dynamically created workbook, that Set srcWS = ThisWorkbook.Sheets("Sheet1") doesn't work because I don't know what the new work book would be called. Is there a way I could avoid needing to know the workbook?
Thanks again! I really appreciate all the help!!

 

[mumps]

Set srcWS = ThisWorkbook.Sheets("Sheet1")

This line of code refers to Sheet1 of the workbook containing the macro. It doesn't refer to the new workbook.