Button to save and name the file based on cell value

[data808]

I have a cell A2 that will have the month entered (JANUARY for example). Then C2 will have the year entered (2022 for example). I have a button that I want to assign a macro to do this for me. If possible, when the macro names the file I would like it to be in this format "ECITATIONS_01JAN_2022.xlsm" for the month of January. February would be "ECITATIONS_02FEB_2022.xlsm" and so on. Right now I have a VBA code that saves and names the file but not based on the value of cells. It just names it to what is in the code and also plugs in the current date.

ActiveWorkbook.SaveAs Filename:="C:\Users\Desktop\" _
& "ECITATIONS_" & Format(Now(), "MM-DD-YYYY") & ".xlsm"

Any help is appreciated. Thanks.

 

[Redhawk1]

This worked for me, you'll have to change some stuff based on active sheet but i followed your methodology for where the date fields are

A2 = the month
B2 = The day
C2 = The year

Option Explicit

Sub SavetheDate()
Dim strMonth As String, Day As Long, Year As Long

With ActiveSheet
strMonth = .Cells(2, 1)
Day = .Cells(2, 2)
Year = .Cells(2, 3)
End With

Debug.Print "ECITATIONS_" & Day & strMonth & Year & ".xlsm"
ActiveWorkbook.SaveAs Filename:="C:\Users\Desktop\" & "ECITATIONS_" & Day & strMonth & Year & ".xlsm"

End Sub

 

[Joe4]

If I understand you correctly, I think this is all you need:

    Dim fname As String
    fname = "C:\Users\Desktop\ECITATIONS_01" & Left(Range("A2"), 3) & "_" & Range("C2") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=fname

 

[data808]

If I understand you correctly, I think this is all you need:

    Dim fname As String
    fname = "C:\Users\Desktop\ECITATIONS_01" & Left(Range("A2"), 3) & "_" & Range("C2") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=fname

Thank you for the help. It's just about correct. I just need the numbers in from of "ECITATIONS" to follow the month. Right now its static to 01 which would be for only January.

 

[Joe4]

Thank you for the help. It's just about correct. I just need the numbers in from of "ECITATIONS" to follow the month. Right now its static to 01 which would be for only January.

Your original code seemed to suggest a day piece. (Format(Now(), "MM-DD-YYYY")) I was assuming that that you wanted the first day, since you did not seem to have a cell for the day.

So, you want the number AND the three letter abbreviation to be for the month, so you would have:
01JAN
02FEB
03MAR
04APR
...

Is that correct?

If so, then try this:

    Dim mnth As String
    Dim fname As String
    mnth = Left(Range("A2"), 3)
    fname = "C:\Users\Desktop\ECITATIONS_" & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("C2") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=fname

 

[data808]

Your original code seemed to suggest a day piece. (Format(Now(), "MM-DD-YYYY")) I was assuming that that you wanted the first day, since you did not seem to have a cell for the day.

So, you want the number AND the three letter abbreviation to be for the month, so you would have:
01JAN
02FEB
03MAR
04APR
...

Is that correct?

If so, then try this:

    Dim mnth As String
    Dim fname As String
    mnth = Left(Range("A2"), 3)
    fname = "C:\Users\Desktop\ECITATIONS_" & Format(DateValue(mnth & "-01"), "mm") & mnth & "_" & Range("C2") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=fname

Brilliant! That is exactly what I wanted. Thank you so much for the help. The 01, 02, 03, etc... was to represent the number month and to help with sorting when the files sit in a folder. I don't like when it sorts alphabetically because then the months are all out of order. Thanks again.

 

[Joe4]

You are welcome.
Glad I was able to help!