VBA save as with new name and overwrite file

yesongm

New Member
Joined
Nov 10, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi everybody!

I am currently trying to save a new file with today’s date in the file name and also delete the old file in the folder.

So there are two things that I would like to solve.
First, If I save the file today the file will be saved as File 10/Nov (example) and the old file that I currently opened and initiated the macro in gets deleted.
Second, If I already have saved the file and I save again later on the same day, the name would be the same, in which case I would like the file to overwrite the file. I wonder if I worded it correctly :(.

Would there be any solution to this?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Welcome to MrExcel forums.

Try this macro. Note that the "/" character is invalid in file names so instead I've used "-" so that the macro saves the file today as "File 11-Nov".

VBA Code:
Public Sub Save_As_New_Workbook_Delete_Old()

    Dim currentFullName As String
    Dim newFullName As String
    Dim newWorkbook As Workbook
    Dim p As Long

    With ThisWorkbook
        currentFullName = .FullName
        p = InStrRev(.FullName, ".")
        newFullName = .Path & "\File " & Format(Date, "dd-Mmm") & Mid(.FullName, p)        
    End With

    'Is the current file name the same as the new file name (i.e. has date changed)?
    
    If StrComp(currentFullName, newFullName, vbTextCompare) <> 0 Then

        'No, so save as new workbook and delete old workbook
        
        ThisWorkbook.SaveCopyAs newFullName

        Set newWorkbook = Workbooks.Open(newFullName)

        ThisWorkbook.Saved = True
        ThisWorkbook.ChangeFileAccess xlReadOnly
        Kill currentFullName
        ThisWorkbook.Close False

    Else

        'Yes, so just save current workbook
        
        ThisWorkbook.Save

    End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top