I’m sure that it’s here somewhere but my apologies. I haven’t been able to complete a search to answer my question.
I’m trying to append a line of code to an existing VBA module that will save the spreadsheet with a filename that contains a reference to the last cell in a column (B) that contains a date. Also the filename should contain the date and time that the file was saved.
e.g. C:\Users\Sam\OneDrive\Statement for 30-Jun-16 and file was saved on 12_11_2016 09 15 PM.xlsm
With the code below I get “True” where I would like the contents of the last cell in column B which contains a date so the result currently looks like this:
C:\Users\Sam\OneDrive\Statement for True and file was saved on 12_11_2016 09 15 PM.xlsm
The format of the date is not important but if it is different from the format of the cell, can it be changed?
The answer is no doubt a simple fix but it is eluding me. Any advice is appreciated.
I’m trying to append a line of code to an existing VBA module that will save the spreadsheet with a filename that contains a reference to the last cell in a column (B) that contains a date. Also the filename should contain the date and time that the file was saved.
e.g. C:\Users\Sam\OneDrive\Statement for 30-Jun-16 and file was saved on 12_11_2016 09 15 PM.xlsm
With the code below I get “True” where I would like the contents of the last cell in column B which contains a date so the result currently looks like this:
C:\Users\Sam\OneDrive\Statement for True and file was saved on 12_11_2016 09 15 PM.xlsm
The format of the date is not important but if it is different from the format of the cell, can it be changed?
The answer is no doubt a simple fix but it is eluding me. Any advice is appreciated.
Code:
ActiveWorkbook.SaveAs Filename:="C:\Users\Sam\OneDrive\Statement for " _
& Range("B6").End(xlDown).Select & " and File was Saved on " & _
Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False