Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

save file based on prevoius month based on date (today)



I  have  a problem  when publish  report  . usually  my  employee  publishes   the  report  in the  ending of current month  this  is  not  problem, but  sometimes   can  happen in the beginning  of next  month for  the previous  month . for  instance  the  report  finishis for  the  AUG month is  08/31/2022    but  the  employee  published   the  report in 1 or 2 or 3 ... for  SEP  month  so  based  on  my  code  when  show  the  name  file will be . for  instance  REPORT 09-2022   but  the  right  name  should  be REPORT 08-2022(this  means after on 28 or on 30 or on 31 based on  ending of  the  month )    . so  how  can  I  add  some  conditions  if  it  happened ,    How can I overcome this problem,please?

Sub copysheet()
    Dim ws As Worksheet, StrPath As String

    Application.DisplayAlerts = False
    'For Each ws In ThisWorkbook.Worksheets
    StrPath = ActiveWorkbook.Path & "\"
    Set ws = sheet2
       ' wb_name = ws.Name
        ActiveWorkbook.SaveAs Filename:= _
            StrPath & "REPORT " & Format(Date, "MM-yyyy") & ".xlsx", FileFormat:=51
    'Next ws
    Application.DisplayAlerts = True
End Sub


Selected Answer

Hi Mussa

I suggest you use Excel's EOMONTH (end of month) function. That always returns the last day of a calendar month, a number of months from a start date (its first argument).

If your report is published within 10 calendar days of month end, put this in a worksheet cell:


and it will give the number equating to 31 Aug (on today 06 Sept). The second argument (here 0) is the number of months plus or minus from the first (date). It will give the same result if the publish date is between the 11th of one month and 10th of the next month. 

In VBA, when creating your filename, replace the Date part with:

Application.WorksheetFunction.EOMONTH(Date-10, 0)

Hope this makes sense and works for you. 



wow !  I  like that ,  this  function has  helped  me  so much
thanks  so  much
Mussa (rep: 32) Sep 6, '22 at 11:29 am
Great. Thanks for selecting my answer, Mussa. 
John_Ru (rep: 3992) Sep 6, '22 at 11:32 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login