SaveCopyAs add new copy if Name already exists

[mellie79]

Hello

I have 3 Macros in my work book. Each one is for a shift(1,2,3) user to save a copy as the following:

ActiveWorkbook.SaveCopyAs ("T:\Mellie79\MFG Schedule\Bulk Synthesis MFG Schedule 1st Shift-" & Format(Now(), "DD-MMM-YYYY") & ".XLSM")

Typically the user would only save once a shift, however in cases were they would save more than once or click the wrong submit button I would like excel to replace the file with the same name plus a verison # or letter. I have searched Google and the forums but have not been successful in making this happen.

Anyone with thoughts?

Thx

 

[Domenic]

How about adding a time stamp to your date...

Format(Now, "yyyy-mm-dd hh-mm-ss")

???

 

[Legal Tender]

I do something similar and use code like this:

'save the file, taking into account that there may already be a file with the intended name, in which case just add "(2)" after it,
'allowing for up to 4 iterations.
    If Len(Dir(folder_to_save_to & file_name_to_save_as & ".xlsm", vbDirectory)) < 1 Then
        ActiveWorkbook.SaveAs Filename:=folder_to_save_to & file_name_to_save_as & ".xlsm", FileFormat:=52
    ElseIf Len(Dir(folder_to_save_to & file_name_to_save_as & "(2)" & ".xlsm", vbDirectory)) < 1 Then
        ActiveWorkbook.SaveAs Filename:=folder_to_save_to & file_name_to_save_as & "(2)" & ".xlsm", FileFormat:=52
    ElseIf Len(Dir(folder_to_save_to & file_name_to_save_as & "(3)" & ".xlsm", vbDirectory)) < 1 Then
        ActiveWorkbook.SaveAs Filename:=folder_to_save_to & file_name_to_save_as & "(3)" & ".xlsm", FileFormat:=52
    ElseIf Len(Dir(folder_to_save_to & file_name_to_save_as & "(4)" & ".xlsm", vbDirectory)) < 1 Then
        ActiveWorkbook.SaveAs Filename:=folder_to_save_to & file_name_to_save_as & "(4)" & ".xlsm", FileFormat:=52
    End If