Macro to save file - save name from cell containing a date using different date formatting

Hi there,

I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.

The format of B5 looks like - 13/08/2014 16:39

The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".

The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14

I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed. After extensive searching for the answer I have decided to ask for help.

Sub SaveReportButton()

Application.DisplayAlerts = False

Dim dtDate As Date
     
dtDate = Range("B5").Value
        
ActiveWorkbook.SaveAs Filename:="I:\Archived Daily Reconciliation\" & Format(dtDate, "yyyy") & _
"\" & Format(dtDate, "mm") & "\" & Format(dtDate, "dd") & "." & Format(dtDate, "mm") & "." & Format(dtDate, "yy") & ".xlsm"
    
Application.DisplayAlerts = True
 
End Sub

Can anyone please assist?

Many thanks,
d_max_c

Thank you for the reply nathansav but it's still not working for me. I have changed the code to read:

dtDate = Worksheets("Master").Range("B5").Value

B5 is definately the source cell but still get the error message. I have noticed however, that with the error message, the active work sheet name changes to the save name rather than saving the file.

I'm not sure how I should apply <>"" and isdate() check either.

Hi nathansav, thanks again, you were right. I got it to work now. I didn't check the file path properly and have amended some folder names to correspond with the code.

However, sheet 'Master' is still being renamed the same as the save name - 13.08.14

I would like to preserve the workbook as is is if possible. Is there anything I can do to prevent this sheet rename?

Many thanks.