Search code examples
vbaexcelsavemonthcalendar

Saving by month to include week number

I am looking to save data from one file to another based on Month, but if a week falls in to another month part way through, I still would like to save the data with the current week. Here's the codes as it stands which works fine for saving by month:

'Copy Data to Workbook Weekly Monthly

Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.DisplayAlerts = True

FileMonth = Month(Date)
StrFileMonth = Format(Date, "MMM")

Filepath = "F:\Customer Services\UK Order File\Monthly Figures\" & StrFileMonth & "\" & "UK_Orders_Monthly.xlsm"

Workbooks.Open (Filepath), _
UpdateLinks:=True

Worksheets("Data").Activate
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Solution

  • A True in VBA is considered -1 when used mathematically (contrary to a worksheet's TRUE which is +1).

    If the weekday(Date) is greater then the day(Date) then the month should be last month; e.g. dateadd("m", -1, Date). If it is equal to or less than the day of the month then the month is the current month; e.g. dateadd("m", 0, Date).

    StrFileMonth = Format(dateadd("m", weekday(date, vbmonday) > day(date), date), "mmm")

    I've used vbMonday where Monday = 1; the WeekDay VBA function defaults to Sunday = 1.