I would like to save
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() ActiveWorkbook.Sheets("Sheet1").Copy 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 ObFD.Execute Application.DisplayAlerts = False Workbooks(FileName & ".xlsm").Close Application.DisplayAlerts = True End If End With End Sub