ActiveWorkbook.SaveAs code runs but fails to save the workbook

Question

I run the code, it asks if I want to save the workbook to the location, I click yes, and then the code ends. I check the folder, the file isn't saved there. I am posting the relevant sections of code here. Note, there are other variables, and code sections that I have not included here. I am working up towards assigning this to a command button on one of the sheets, and want to eventually have the No answer to lead to the next step in the process where sections of multiple sheets will be saved to a PDF and then print those same reports. I have further issues on that but once I resolve this immediate pressing issue, I'll come back if I can't fix the other one.

Sub mac_CreateProposalFolder()

    Dim varFilePathDir As Range
    Set varFilePathDir = Range("cell_FilePathDrive")

    Dim varFilePathProjYear As Range
    Set varFilePathProjYear = Range("cell_FilePathYear")

    Dim varFilePathProjYNum As Range
    Set varFilePathProjYNum = Range("cell_FilePathYearSuffix")

    Dim varFilePathProjName As Range
    Set varFilePathProjName = Range("cell_DDGJobName")

    Dim varFilePathSubFold1 As Range
    Set varFilePathSubFold1 = Range("cell_FilePathSubFolder1")

    Dim varBusinessUnit As Range
    Set varBusinessUnit = Range("cell_BusinessUnit")

    Dim varDDGJobNumber As Range
    Set varDDGJobNumber = Range("cell_DDGJobNumber")

    Dim varProposalDate As Range
    Set varProposalDate = Range("cell_ProposalDate")

    Dim varFilePathSaveAs1Survey As String
    Dim varFilePath1Survey As String

    Dim varAnswer5Survey As VbMsgBoxResult

    varFilePath1Survey = varFilePathDir & varFilePathProjYear & "-000\" & varFilePathProjYear & varFilePathProjYNum & " " & varFilePathProjName
    
    varFilePathSaveAs1Survey = varFilePath1Survey & "\" & varFilePathSubFold1 & "\" & varBusinessUnit & " Proposal for " & varDDGJobNumber & " " & varFilePathProjName & " on " & varProposalDate
    
        'Checks if the proposal file has been created for this project and asks if the user wishes to save the file if not, or cancel the operation.
        If VBA.FileSystem.Dir(varFilePathSaveAs1Survey) = vbNullString Then
            varAnswer5Survey = MsgBox("Excel file for this business unit and proposal date has not been saved to this folder yet. Do you wish to save this excel file to the folder?", vbYesNoCancel, "Save Excel Workbook?")
            Select Case varAnswer5Survey
                Case vbYes
                    ActiveWorkbook.SaveAs Filename:=varFilePathSaveAs1Survey & ".xlsm"
                Case vbNo
                    'saved spot for future code options.
                    Exit Sub
                Case vbCancel
                    Exit Sub
            End Select
        End If
        On Error Resume Next
End Sub

I have attempted multiple methods for the save as - using ThisWorkbook.SaveAs in place of ActiveWorkbook.SaveAs (preference is 'Active' not 'This' because the workbook will be a generic one that gets saved to the folder as the active one for this project and this date);

ActiveWorkbook.SaveAs ([varFilePathSaveAs1Survey & ".xlsm", xlOpenXMLWorkbookMacroEnabled]); 
ActiveWorkbook.SaveAs Filename:= varFilePathSaveAs1Survey & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled; ActiveWorkbook.SaveAs ([Filename:= varFilePathSaveAs1Survey & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled])

and multiple other configurations. I even recorded a macro and copied the text from that one while altering the desktop location for saving the macro to the preferred location:

    ChDir "C:\Users\******\OneDrive - **********, PC\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "https://******-
my.sharepoint.com/personal/******/Documents/Desktop/PROJECT%20BUDGET-Rough%20Draft%20V1.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Note: ***** are scrubbed sections of links due to safeguarding internal information.

So far, all the typical methods recommended have not worked and while the code compiles, it just won't save.

I am using Windows 11 on a Dell Precision, Microsoft® Excel® for Microsoft 365 MSO (Version 2302....64 bit)

Edit: solution to the issue lay in the date formatting - the cell had it formatted in "YYYY-MM-DD" but the code was reading it as mm\dd\yyyy and it was erroring due to attempting to find a mm directory, then dd directory, then yyyy directory. Changing the date format fixed the error.

Answer 1

If you don't get any error on saving but the file isn't where you expect, then

MsgBox ActiveWorkbook.FullName

immediately after calling SaveAs will show you where the workbook actually got saved. That may help you fix your code.

From Scott

Format(varProposalDate, "YYYY-MM-DD")

will give you the date in a format that useable in a file path or file name.