(Solved) – Save a new Excel file to a user-given path with FileDialog (msoFileDialogSaveAs)


I would like to save Sheet1 of ActiveWorkbook in a new book, the path will be given by the user and the name will be given by me, and then close the new book.

First, I create the copy of the sheet in a new book: ActiveWorkbook.Sheets("Sheet1").Copy and then call FileDialog (msoFileDialogSaveAs). But this saves ActiveWorkbook with the new name and leaves the newly created book unsaved and Workbooks(FileName & ".xlsm").Close gives error.

What am I doing wrong?

Option Explicit

Sub Save_As()
    Dim Time As String
    Time = Format(Now, "ddmmyyyy_hhmmss")
    Dim FileName As String
    FileName = ActiveWorkbook.Name & "_" & Time
    Dim ObFD As FileDialog
    Set ObFD = Application.FileDialog(msoFileDialogSaveAs)
    With ObFD
        .Title = "Choose a path and export this file"
        .ButtonName = "E&xport"
        ObFD.InitialFileName = "C:" & FileName
        ObFD.FilterIndex = 2
        ObFD.InitialView = msoFileDialogViewDetails
        If ObFD.Show <> 0 Then
            Application.DisplayAlerts = False
            Workbooks(FileName & ".xlsm").Close
            Application.DisplayAlerts = True
        End If
    End With
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *