VBA Macro ActiveWorkbook.SaveCopyAs Run-time error '1004'

[damrkstr]

Hi All, I have been trying to search in the existing threads for a solution to my question and cannot seem to find one. I am trying to create this generic SaveCopyAs macro to save a copy of my current file with the current date in a "Superseded" folder in the current directory where my file is saved, and I keep getting a run-time error saying that the file could not be found and "Is it possible it was moved, renamed or deleted?". The "Superseded" folder already exists. Any help you could provide in getting this code to work would be much appreciated! thank you!

Sub Supersede_Copy()
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path & "\Superseded\" & Format(Now, "yyyy-mm-dd") & " " & ActiveWorkbook.Name
End Sub

 

[Joe4]

Welcome to the Board!

I would first verify that the file name is being built correctly.
One way to do it is like this:

Sub Supersede_Copy()
    Dim fname as String
    fname = ActiveWorkbook.Path & "\Superseded\" & Format(Now, "yyyy-mm-dd") & " " & ActiveWorkbook.Name
    MsgBox fname
    ActiveWorkbook.SaveCopyAs Filename:=fname
End Sub

Run this code, and when the MsgBox pops up, check to make sure that the file name is accurate, with nothing misspelled or anything like that.

If it looks good, see if the save step works. I think the issue might be the space in the file name. Storing the file name as a variable may correct that.
If not, try changing the space you are adding to your file name to an underscore.

 

[damrkstr]

Welcome to the Board!

I would first verify that the file name is being built correctly.
One way to do it is like this:

Sub Supersede_Copy()
    Dim fname as String
    fname = ActiveWorkbook.Path & "\Superseded\" & Format(Now, "yyyy-mm-dd") & " " & ActiveWorkbook.Name
    MsgBox fname
    ActiveWorkbook.SaveCopyAs Filename:=fname
End Sub

Run this code, and when the MsgBox pops up, check to make sure that the file name is accurate, with nothing misspelled or anything like that.

If it looks good, see if the save step works. I think the issue might be the space in the file name. Storing the file name as a variable may correct that.
If not, try changing the space you are adding to your file name to an underscore.

Joe4, thank you very much for your reply. I tried your code, and the file path looks correct in the message box, but it is still not saving. I tried using an underscore for the space or removing the space altogether and still no luck. I am still getting the same error, but now it is highlighting the "ActiveWorkbook.SaveCopyAs Filename:=fname" line. Here is my current code without any space or underscore between the date and the workbook name. Thank you very much for your help!

Sub Supersede_Copy()
    Dim fname As String
    fname = ActiveWorkbook.Path & "/Superseded/" & Format(Now, "yyyy-mm-dd") & ActiveWorkbook.Name
    MsgBox fname
    ActiveWorkbook.SaveCopyAs Filename:=fname
End Sub

 

[Joe4]

Do you have rights to dave to that folder?
Try manually saving a file there, and see if it works.

 

[damrkstr]

Do you have rights to dave to that folder?
Try manually saving a file there, and see if it works.

Yes I do have the rights. It's really interesting actually, I just tested your code, and even my original code changing the .SaveCopyAs to .SaveAs and it seems to save perfectly. But unfortunately that doesn't help, since the whole point is to keep my current file active and just supersede a copy of the file. At least that shows us that the code is correct otherwise. I wonder why the SaveCopyAs code is not working? The working code with the "ActiveWorkbook..SaveAs" is below. Thank you for your help.

Sub Supersede_Copy()
    Dim fname As Variant
    fname = ActiveWorkbook.Path & "/Superseded/" & Format(Now, "yyyy-mm-dd") & ActiveWorkbook.Name
    MsgBox fname
    ActiveWorkbook.SaveAs Filename:=fname
End Sub

 

[Joe4]

So I tested out your code, and found a problem. Your slashes are backwards.
This:

"/Superseded/"

should be this:

"\Superseded\"

 

[damrkstr]

So I tested out your code, and found a problem. Your slashes are backwards.
This:

"/Superseded/"

should be this:

"\Superseded\"

Thank you Joe4 for your persistence. I tried that and it is still not working unfortunately. I also tried just removing the "Superseded" folder and trying to save to the active workbook.path without modifications and that is still not working with the SaveCopyAs, but it does work with the ActiveWorkbook.SaveAs. That seems to be the problem for some reason. Is there another alternative to achieve the same result as SaveCopyAs? Thank you!

 

[Joe4]

Yes, there are a few. You could do one of the following:
1. Copy the data to a new workbook and save that (this really works best if you are NOT copying over VBA code along with it)
2. First do a Save, then do a SaveAs, then re-open the original one, and close the one you just saved.

 

[damrkstr]

Yes, there are a few. You could do one of the following:
1. Copy the data to a new workbook and save that (this really works best if you are NOT copying over VBA code along with it)
2. First do a Save, then do a SaveAs, then re-open the original one, and close the one you just saved.

Thank you. Unfortunately I was looking for a macro supersede button option where I don't have to exit my current file. I am working in several excel models, and I often like to quickly supersede a copy before I make a major modification. For some reason the SaveCopyAs code works when I specify an explicit path, but not when I am trying to make it generic as you saw with the active path and workbook name. Thank you for your help.

 

[Joe4]

I am not really sure what to tell you. When I test your code (after the slash correction), it works perfectly fine for me. So I cannot reproduce the behavior you are talking about.
Can you do me one last favor? Can you post the full path and file name that is being brought back by the message box?
I think that there may be a limit to the maxiumum file name path and length, so I would be interested to see how many characters it is.