VBA Save as on desktop with previous month's first and last dates

[cdalgorta]

Hi,
Still beginner here.
I've been trying to find the way with Google, but there are too many different response and none have worked for me so far.
I need to save a file with this name(based on today being August 2022) to my desktop:

"2022-07-01 to 2022-07-31 Consolidated file.xlsx"

"The first date for last month" "to" "the last date of last month" "Consolidated file"​

I've tried this last(which I'm sure is totally wrong hahah), but I still get error on the last line.
Please help if you can.
Thank you in advance!


Sub testingSaveAs()

Dim wb As Workbook
Dim mydate As Date
mydate = Now()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
FirstDay = Format(DateSerial(Year(mydate), Month(mydate), 1), "YYYY-MM-DD")
LastDay = Format(DateSerial(Year(mydate), Month(mydate), 1) - 1, "YYYY-MM-DD")



wb.SaveAs Filename:=desktop & FirstDay & " to " & LastDay & " Consolidated File Peloton.xlsx"

End Sub


I thought it would be easy and all I'd need to do is use the eomonth function for the format:
FirstDay = Format(EOMONTH(NOW(),-2)+1, "YYYY-MM-DD")
but it gave me error on the Eomonth for some reason.

 

[Siddharth Rout]

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim wb                      As Workbook
    Dim FlName                  As String
    Dim FilePath                As String
    Dim FirstDayOfLastMonth     As String
    Dim LastDayOfLastMonth      As String
    
    Set wb = ThisWorkbook
    
    FilePath = "C:\Users\" & Environ("Username") & "\Desktop\"
    
    FirstDayOfLastMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "YYYY-MM-DD")
    LastDayOfLastMonth = Format(DateSerial(Year(Date), Month(Date), 0), "YYYY-MM-DD")

    FlName = FilePath & FirstDayOfLastMonth & " to " & LastDayOfLastMonth & " Consolidated File Peloton.xlsx"

    Debug.Print FlName
    'OUTPUT: C:\Users\Username\Desktop\2022-07-01 to 2022-07-31 Consolidated File Peloton.xlsx
    
    'wb.SaveAs Filename:=FlName, FileFormat:=51
End Sub

Few other pointers:

1. Avoid using too many variables.
2. Always declare your variables.
3. While using .SaveAs, do not skip FileFormat.

 

[cdalgorta]

Hi,
Still beginner here.
I've been trying to find the way with Google, but there are too many different response and none have worked for me so far.
I need to save a file with this name(based on today being August 2022) to my desktop:

"2022-07-01 to 2022-07-31 Consolidated file.xlsx"

"The first date for last month" "to" "the last date of last month" "Consolidated file"​

I've tried this last(which I'm sure is totally wrong hahah), but I still get error on the last line.
Please help if you can.
Thank you in advance!


Sub testingSaveAs()

Dim wb As Workbook
Dim mydate As Date
mydate = Now()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
FirstDay = Format(DateSerial(Year(mydate), Month(mydate), 1), "YYYY-MM-DD")
LastDay = Format(DateSerial(Year(mydate), Month(mydate), 1) - 1, "YYYY-MM-DD")



wb.SaveAs Filename:=desktop & FirstDay & " to " & LastDay & " Consolidated File Peloton.xlsx"

End Sub


I thought it would be easy and all I'd need to do is use the eomonth function for the format:
FirstDay = Format(EOMONTH(NOW(),-2)+1, "YYYY-MM-DD")
but it gave me error on the Eomonth for some reason.

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim wb                      As Workbook
    Dim FlName                  As String
    Dim FilePath                As String
    Dim FirstDayOfLastMonth     As String
    Dim LastDayOfLastMonth      As String
   
    Set wb = ThisWorkbook
   
    FilePath = "C:\Users\" & Environ("Username") & "\Desktop\"
   
    FirstDayOfLastMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "YYYY-MM-DD")
    LastDayOfLastMonth = Format(DateSerial(Year(Date), Month(Date), 0), "YYYY-MM-DD")

    FlName = FilePath & FirstDayOfLastMonth & " to " & LastDayOfLastMonth & " Consolidated File Peloton.xlsx"

    Debug.Print FlName
    'OUTPUT: C:\Users\Username\Desktop\2022-07-01 to 2022-07-31 Consolidated File Peloton.xlsx
   
    'wb.SaveAs Filename:=FlName, FileFormat:=51
End Sub

Few other pointers:

1. Avoid using too many variables.
2. Always declare your variables.
3. While using .SaveAs, do not skip FileFormat.

Thank you so much Siddharth!
I'll follow your pointers from now on