Save File with ordinal date (1st, 2nd, 3rd) Name

[laugil]

Hello all,

I've been trying everything and searching without luck. I'm trying to save a file with an Ordinal date (1st, 2nd, 3rd). I need the name to look like Summary June 10th to June 16th.

The code I have so far is:


ActiveWorkbook.SaveAs ("C:\Users\Laura" _ & "Summary " & Application.WorksheetFunction.Proper(Application.WorksheetFunction.Text(Now() - 7, "Mmmm")) _
& " ") & Day(Now() - 7) & " " & "to" & " " _
& Application.WorksheetFunction.Proper(Application.WorksheetFunction.Text(Now() - 7, "Mmmm")) _
& " " & Day(Now() - 1) & ".xlsx"


Please let me know if you need me to be more clear!

Regards,

 

[mikerickson]

SheetName = Format(Now() - 7, "mmmm d")
Select Case Day(Now() - 7)
    Case 1
        SheetName = SheetName & "st"
    Case 2
        SheetName = SheetName & "nd"
    Case 3
        SheetName = SheetName & "rd"
    Case Else
        SheetName = SheetName & "th"
End Select

SheetName = SheetName & " to " & Format(Now()-1, "mmmm d")
Select Case Day(Now() - 1)
    Case 1
        SheetName = SheetName & "st"
    Case 2
        SheetName = SheetName & "nd"
    Case 3
        SheetName = SheetName & "rd"
    Case Else
        SheetName = SheetName & "th"
End Select

ActiveWorkbook.SaveAs "C:\Users\LauraSummary " & SheetName & ".xlsx"

 

[Tetra201]

Try this:

Dim DateStr(1 To 2) As String, ii As Byte, xx As String
For ii = 1 To 2 Step 1
    Select Case (Day(Date - 7 + (ii - 1) * 6) Mod 30) Mod 20
        Case 1: xx = "st"
        Case 2: xx = "nd"
        Case 3: xx = "rd"
        Case Else: xx = "th"
    End Select
    DateStr(ii) = Format(Date - 7 + (ii - 1) * 6, "mmm d") & xx
Next ii
ActiveWorkbook.SaveAs "C:\Users\Laura\Desktop\Summary " & DateStr(1) & " to " & DateStr(2) & ".xlsx"