Extract Date & Year

[howard]

I have the following code to extract the current month - 1 month as well as the year

 .body = "Attached please find Sales Data by region as at " & Format(Month(Date) - 1 & " " & Year(Date), "mmm yyyy") & " vs the Prior Year" & vbNewLine & vbNewLine

I would like this amended to extract the last date and year from row 1 on sheet "Sales Summary"

for eg a1 oct 2017, B1 Nov 2017, C1 Dec 2017

As Dec 2017 is the last row in row 1 containing a date and year , I would like Dec 2017 in this example to be extracted . If the last date was Jan 2018, then Jan 2018 in tow 1 to be extracted


it would be appreciated if someone could kindly amend my code

 

[alansidman]

Not knowing how your data is formatted, I have developed this code assuming your dates are in standard date form

Option Explicit


Sub lastDate()
Dim lc As Long
lc = Sheets("Sales Summary").Cells(1, Columns.Count).End(xlToLeft).Column
Dim LDate As Long
LDate = Cells(1, lc).Value
MsgBox ("Last Date is " & Format(LDate, "mm/dd/yyyy"))
End Sub

 

[howard]

Thanks for the help