Save files in accordance of month etc

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Evening.
I have supplied the code below that is in use & works as it should.


Code:
Private Sub GrassSummarySheet_Click()    Dim strFileName As String
    
    strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\" & Range("A3") & " " & Range("D3") & ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
        Exit Sub
    End If
    
    With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
        MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
        Range("A5:B41").ClearContents
        Range("A5").Select
        ActiveWorkbook.Save
    End With
End Sub

All the above works fine but i need to think about how the files etc look in the saved foler.
I mean currently i see this
APRIL,JUNE,MAY

As the months sheets are added i will see December towards the very start.

I cant explain things correctly but if they could be save with a number in front the it would be in the correct order in the saved folder like so,
04 APRIL
05 MAY
06 JUNE
07 JULY
08 AUGUST
09 SEPTEMBER
10 OCTOBER
11 NOVEMBER
12 DECEMBER
13 JANUARY
14 FEBRUARY
15 MARCH
16 APRIL
This would be the full year save in the correct month order as the numbers would be save in the numerical sequence.

Otherwise it will be like,
APRIL
AUGUST
DECEMBER
JANUARY
JUNE
JULY
etc etc

many thanks
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I needed the save file like JULY 2019 but spending some time today it was everything but so had to split them up
 
Upvote 0
How about saving them the other way around
Code:
    strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\" & _
                  Range("D3") & "_" & Format(Month(DateValue(Range("A3") & " 1, " & "2019")), "00") & " " & Range("A3")
 
Upvote 0
Nice but not quite.
Reason being June & July come before May
 
Upvote 0
After you added the file extension, were the file names not
2019_05 May.pdf
2019_06 June.pdf
2019_07 July.pdf
 
Upvote 0
Sorry my mistake.
yes you are correct it works fine.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top