Hi guys,
I have this code that saves a copy of the workbook and names it with the date in range "AK5" in a specified directory and subsequent year and month folder, however, it selects the current year and month folder when saving..
I would like the code to save the file in the year and month folder of the date in range "AK5" and not current.
Thank you very much in advance!
Dim dt As String: dt = Format(CStr(Range("AK5")), "dd_mm_yyyy")
Dim strGenericFilePath As String: strGenericFilePath = "P:\Reports\"
Dim strYear As String: strYear = Year(Date) & "\"
Dim strMonth As String: strMonth = MonthName(Month(Date)) & "\"
Dim strMonthNumber As String: strMonthNumber = Month(Date) & "."
Dim strDay As String: strDay = Day(Date) & "\"
Dim strFileName As String: strFileName = "Weekly Report_wc_" & dt
Sheets("Overview").Select
Range("A1").Select
Application.DisplayAlerts = False
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear
End If
If Len(Dir(strGenericFilePath & strYear & strMonthNumber & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonthNumber & strMonth
End If
ActiveWorkbook.SaveAs FileName:= _
strGenericFilePath & strYear & strMonthNumber & strMonth & strFileName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox "File Saved in : " & vbNewLine & strGenericFilePath & strYear & strMonth & strFileName
Bookmarks