Date Series - first date of the Week

Charlie987

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
Try:
VBA Code:
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

New Member
Joined
Jul 25, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,307
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,879
Messages
5,638,800
Members
417,053
Latest member
SaturdayNight

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top