SaveCopyAs error 1004

[Jat999]

Hi all

I am struggling with a runtime 1004 error with the routine below.

I want to save a copy of the current workbook into a sub folder. The code checks the sub directory exists and if not creates it. This works OK, Its the last line of the script that fails.

Any advice please.

Private Sub Save_Workbook()
 
Dim Wbook_Name As String
Dim New_Wbook_Name As String
Dim Cur_Path As String
Dim strPath As String
 
Wbook_Name = ThisWorkbook.Name
Cur_Path = ThisWorkbook.Path
strPath = Cur_Path & "\Archive"
        If Dir(strPath, vbDirectory) = "" Then
            MkDir strPath
        End If      
 
New_Wbook_Name = strPath & "\" & Now & "_" & Wbook_Name
ActiveWorkbook.SaveCopyAs Filename:=New_Wbook_Name
 
End Sub

Thanks in advance

John

 

[Joe4]

The problem is that your New_Wbook_Name uses "Now", and that returns slashes in the date. You cannot use those in file names (it might actually confuse them with subdirectories).
You are going to need to format the Now portion, i.e. maybe something like this:

New_Wbook_Name = strPath & "" & [COLOR=#ff0000][B]Format(Now, "yyyymmdd_hhmmss")[/B][/COLOR] & "_" & Wbook_Name

 

[Jat999]

Hi Joe4

Thanks - worked brilliantly


John

 

[Joe4]

You are welcome.