Hello,
I have a short excel sheet where different departments have to input some data.
I have a “save as” macro so that we have a standard/sequential file name:
Sub saveas()
‘
‘
If MsgBox(“blablabla”, vbYesNo) = vbNo Then Exit Sub
‘
With Application
.DefaultFilePath = “C:”
End With
Sheets(“Sheet1”).Select
ThisFile = Range(“E40”).Value
FileDrive = Range(“E41”).Value
ActiveWorkbook.saveas Filename:=FileDrive & ThisFile & “.xls”, _
FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
Range(“A1”).Select
End Sub
E41 has the standard path written (a shared drive).
E40 has a formula that creates a filename from the data entered in the sheet.
As this filename may change a bit everytime a different Dept. enters pieces of data, saving it every time with this macro is a good tool for showing what the completeness status is for these forms, just by having a glance at the filename.
My problem is that this generates a lot of duplicate files, since it only creates a new file, not replacing the old one.
I have:
1xxxxxx.xls
2xxxxxx.xls
3xxxxxx.xls
…where I just wanted to have the 3xxxxxx.xls, which is the most recent one.
is it possible to have this macro work in a way so that if the filename has changed it just replaces the current name instead of duplicating?
Thanks in advance,
Tiago
