Copy sheet to new Workbook (ReNaming + Macro)

[MicroDude]

Hi everyone,

I have created a rudimentary excel workbook with macros, and the main idea behind it is to fill out a form, and then you can click a button which runs a macro module; which in turn saves the current sheet to a server, and automatically sends an email to the Administrators - warning them that a new form is ready to be published.

Sub Module3()
Application.ScreenUpdating = False

Worksheets("LO FORM").Copy
With ActiveWorkbook
     .SaveAs Filename:=("**SERVERPATH**") & "\LOFORM.xlsx", FileFormat:=xlOpenXMLWorkbook
     .Close SaveChanges:=False
     
Application.ScreenUpdating = True

End With 'At this point the Mail macro runs'

How ever the problem I'm having is that I do not know how to make the file rename it self, as of now only one user can save the file, and if another tries he will only get a warning that the file already exists, and you can only overwrite or cancel the process. So my question is, is there a way I can tell the macro to rename the file if another file with the same name already exists?

Thanks in advance,

[Caleeco]

Hello MicroDude

How many forms get created each day? You could code the macro to add the date to the file name, and then if multiple forms are created each day you can then also add the current 'time' to the filename.

This would create somewhat unique file names. Failing that the function below will check if the files exists, just pass it the filename (fname) and define the serverpath variable

Function CheckExists(fname As String) As Boolean
Dim TestStr As String
Dim fpath As String

fpath = Worksheets("Filepaths & Filenames").Range("D4").Value

TestStr = ""
On Error Resume Next
TestStr = Dir(fpath & "\" & fname & ".xlsm")

On Error GoTo 0
If TestStr = "" Then
        'MsgBox "File doesn't exist"
        CheckExists = False
Else
        'MsgBox "File exist"
        CheckExists = True
End If

End Function

Caleeco

[MicroDude]

Hi Caleeco,

Thanks for the quick answer, I really appreciate it. I've been trying to implement this code into my original piece, but it just doesn't want to work. Do I need to create a new module and insert the function, and then link the original (COPY) module to run that function as a check? And if so how? I'd gladly appreciate any help!

Thanks again!

[MicroDude]

Shameless self-bump..