SOLVED - "Method 'SaveAs' of object '_Workbook' failed" (1004) when saving into same file location

NikolinoDE 

Update: 

So, the variable "PathAndFile_Name" is the defined path and name/type in the SaveAs dialog. For example, is "C:\Users\MY NAME\Desktop\CUSTOM NAME.xlsx"

And, the variable "File_Name" is just the defined name/type in the SaveAs dialog. For example, "CUSTOM NAME.xlsx"

Based on most of the internet's examples, I thought the "FileName:=" was to include the full path. But, while still getting the runtime error 1004 and reading https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas more carefully, I tried using just "File_Name" instead of "PathAndFile_Name" in "ActiveWorkbook.SaveAs" (line 48 below).

I don't know how or why but I solved it by changing "PathAndFile_Name" to just "File_Name", and it will no longer produce an error when saving in the same file location as the main ".xlsm" workbook and still works with other user-selected file locations.

What's weird is using the full path in "ActiveWorkbook.SaveAs FileName:=" works in every way but the same file location as the main .xlsm. But, just using the name works in any location. If someone understands why I'd love to know, but regardless am glad it works.

For anyone looking for a complete SaveAs code (using "Application.FileDialog(msoFileDialogSaveAs)"), feel free to paste this working example into your project then edit as needed:

Sub SaveAs_YourFile()

'Set then launch SaveAs dialog (YOU CAN EDIT THIS AS NEEDED):
    On Error GoTo SaveAs_Error_Handler
    Application.ScreenUpdating = False
    
    Dim ObFD As FileDialog
    Dim File_Name As String
    Dim PathAndFile_Name As String
    File_Name = "YOUR DEFAULT NAME" 'Set default (suggested) File Name
    
    Set ObFD = Application.FileDialog(msoFileDialogSaveAs)
    With ObFD
        .Title = "Save As - YOUR PROJECT NAME"
        .ButtonName = "S&ave"
        .InitialFileName = ThisWorkbook.Path & "\" & File_Name 'Default file location and File Name
        .FilterIndex = 1 'File Type (.xlsx)
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.count = 0 Then
        MsgBox "Save As canceled", vbExclamation, "Save As - YOUR PROJECT NAME"
        Application.ScreenUpdating = True: Exit Sub
        Else
        PathAndFile_Name = .SelectedItems(1)
        End If
    End With

'Verify file type (YOU CAN EDIT/REMOVE THIS AS NEEDED):
    If Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, ".") + 1) <> ".xlsx" Then
    MsgBox "Note: You can only save as an ""Excel Workbook (*.xlsx)"" file type. The file type will be changed from """ _
    & Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, ".") + 1) & """ to " & """.xlsx""." _
    , vbExclamation, "Save As - YOUR PROJECT NAME"
    PathAndFile_Name = Replace(PathAndFile_Name, Right(PathAndFile_Name, Len(PathAndFile_Name) - _
    InStrRev(PathAndFile_Name, ".") + 1), ".xlsx"): End If 'Check if file type is not .xlsx
    If Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "\")) = ".xlsx" Then
    PathAndFile_Name = Left(PathAndFile_Name, InStrRev(PathAndFile_Name, Application.PathSeparator)) _
    & Replace(File_Name, ".xlsm", ".xlsx"): End If 'Check if file name is literally just .xlsx

'Copy YOUR SHEET into new workbook then edit (YOU CAN EDIT THIS AS NEEDED):
    Application.DisplayAlerts = False
    File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "\")) 'Update File Name to user's input
    ThisWorkbook.Sheets("" & Sheet1.Name & "").Copy 'SPECIFY THIS AS NEEDED
    'Note: The Sheets.copy is what creates a new workbook, thus the following code to save it then work _
    with it. With this method, only one sheet copy occurs at a time with the applicable year loaded _
    beforehand. "If you don't specify either Before or After, Microsoft Excel creates a new workbook _
    that contains the copied Worksheet object." (https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.copy)
    ActiveWorkbook.SaveAs fileName:=File_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate
    
    With Excel.Workbooks(File_Name).Sheets
    '<YOUR CODE TO EDIT THE NEW WORKBOOK SHEET HERE>
    End With

    Excel.Workbooks(File_Name).Activate
    Excel.Workbooks(File_Name).Close SaveChanges:=True 'New workbook save and close
    Application.DisplayAlerts = True
    'Note: For full name of new workbook: "ObFD.InitialFile_Name"
    'For more information about Excel file types: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat
    'Original Code: https://stackoverflow.com/questions/60696187/save-a-new-excel-file-to-a-user-given-path-with-filedialog-msofiledialogsaveas

'Error handler (YOU CAN EDIT THIS AS NEEDED):
If 1 = 2 Then
SaveAs_Error_Handler:
    Application.ScreenUpdating = True: Application.DisplayAlerts = True
    MsgBox "An unexpected error occurred while saving " & File_Name & "!", vbCritical, "Save As - YOUR PROJECT NAME"
Else
    Application.ScreenUpdating = True
    MsgBox File_Name & " saved successfully.", vbInformation, "Save As - YOUR PROJECT NAME"
End If

End Sub