EXCELY


Free Excel Templates and Articles
  • Excel VBA
  • Save Workbook as New File using VBA in Excel

Save Workbook as New File using VBA in Excel



Q. How Save Workbook as New File?

A. Use the following VBA code:

Private Sub SaveWorkbookAsNewFile(NewFileName As String)
    Dim ActSheet As Worksheet
    Dim ActBook As Workbook
    Dim CurrentFile As String
    Dim NewFileType As String
    Dim NewFile As String
 
    Application.ScreenUpdating = False    ' Prevents screen refreshing.

    CurrentFile = ThisWorkbook.FullName
 
    NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
               "Excel Files 2007 (*.xlsx), *.xlsx," & _
               "All files (*.*), *.*"
 
    NewFile = Application.GetSaveAsFilename( _
        InitialFileName:=NewFileName, _
        fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:= NewFile, _
            FileFormat:=xlNormal, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False
 
        Set ActBook = ActiveWorkbook
        Workbooks.Open CurrentFile
        ActBook.Close
    End If
 
    Application.ScreenUpdating = True
End Sub

How does it work? Let's look inside.

First, turn off screen updating:

Application.ScreenUpdating = False

Store the opened file full path:

CurrentFile = ThisWorkbook.FullName

Open window to choose new filename and folder:

NewFile = Application.GetSaveAsFilename( _
    InitialFileName:=NewFileName, _
    fileFilter:=NewFileType)

And now save file as new Workbook:

ActiveWorkbook.SaveAs Filename:= NewFile, _
    FileFormat:=xlNormal, _
    Password:="", _
    WriteResPassword:="", _
    ReadOnlyRecommended:=False, _
    CreateBackup:=False

We have to close new file and open the origin workbook:

Set ActBook = ActiveWorkbook
Workbooks.Open CurrentFile
ActBook.Close

and turn on screen updating:

Application.ScreenUpdating = True