I can get rid of the popup/warning about overwriting an existing file, but I just can't figure out how to get the macro NOT to overwrite the file if it already exists. A coworker ended up deleting a couple days worth of data entry the beginning of this month, so it was no big deal at that time (barely an hours worth of work this time), but I want to prevent a complete loss of the month if they decide to do it again later.
I've included the entire code below, but have highlighted the coding I'm working with in red below. So long as I leave out the red coding, the popup comes up, but nobody reads it, so the file gets overwritten
Thanks in advance for your help
Sub OpenAndSaveNew31()
'
' OpenAndSaveNew Macro
'
' This saves a copy of the current file into the backup folder
ActiveWorkbook.Save
Application.ScreenUpdating = False
' Define source file name, not including path.
SourceFile = UCase(Format(DateAdd("m", -1, Date), "YYYY-MM")) & ".xlsm"
' Define target file name. Include path if needed.
DestinationFile = "C:\Users\UserName\DestFolder\" & UCase(Format(DateAdd("m", -1, Date), "YYYY-MM")) & ".xlsm"
' Copy source to target.
Workbooks(SourceFile).SaveCopyAs DestinationFile
' This opens the 31-Day workbook
Workbooks.Open Filename:="C:\Users\UserName\SourceFolder\"
ActiveWorkbook.RunAutoMacros xlAutoOpen
' This will save the workbook by the current year and month
Dim sFile As String
sFile = Format(Now(), "yyyy-mm") & ".xlsm" ' generates the file name for the current year-month
Application.DisplayAlerts = False '<-- Automatically overwrites the file if it exists already, don't want it, change to not overwrite existing AND no popup
ActiveWorkbook.SaveAs Filename:="C:\Users\UserName\SourceFolder\" & sFile
' This will create a shortcut of the saved file in U:\shortcuts
Filename = "U:\shortcuts\" & sFile & ".lnk" ' link name
With CreateObject("WScript.Shell").CreateShortcut(Filename)
.TargetPath = "C:\Users\UserName\SourceFolder\" & sFile
.Save
End With
' This will save and close the workbook just created
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Bookmarks