How to Rename Workbook at the end of the Month with VBA

How to Rename Workbook at the end of the Month with VBA

Sometimes you want of quickly rename an excel file to some specific name. You may want to do this to save time.

There are many ways to achieve this; you can go through the normal everyday procedure:

• Look for file
• Right-click it and choose rename
• Rename it and
• Press Enter Key

Or you can just follow the step(s) below:

• While file is open, press a single button to rename

This code snippet is how you rename an excel file at the end of the month by adding the date to the name of the file.

Continue if you have enabled developer options

• Insert a new Module and enter the following codes

 
 Option Explicit   
  
 Private Sub RenameWorkbookAtMonthEnd()
 Dim wbName As String
 Dim dtMonth As Date
  wbName = ThisWorkbook.Name ' Get the Name of the workbook
  Call RenameWrkbk(wbName)   ' Pass wbName to sub to rename the Workbook
 End Sub

This first macro is used to first get the name of the workbook which is the passed to another macro to rename the file

 
Sub RenameWrkbk(wbName As String)
Dim day As String
day = Format(getNow, "dd") ' Get the day from the "getNow" function
    If day = "31" Or day = "30" Then  ' Verify for end of Month
    ThisWorkbook.SaveAs Filename:=getNewFileName(wbName),FileFormat:=ThisWorkbook.FileFormat            
    ' use the getNewFilename function to get a new name for the file
        Else: Exit Sub
        MsgBox "NOT END OF MONTH" ' Show Message box if it is not the end of the month
    End If
End Sub

This second macro is used to actually rename file with the getNewFileName() Function

 
Function getNewFileName(wbName As String) As String
    Dim fname As String
    Dim mntName As String
    fname = ""
    mntName = Format(getNow, "mmm-dd")
    fname = fname + Replace(wbName, ".xlsm", "") + mntName
    getNewFileName = fname
End Function
Function getNow() As Date
    getNow = Now()
End Function

Once done, you can either run the code directly or add it to a button