Save macro not working

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
528
I have a spreadsheet that the user will enter a number in a cell, which triggers a macro to search the directory and find the parent folder and then place the file path in a designated cell (CU1). When the user selects the save button on the screen, I have the macro check a cell to see if the file has been saved before, and if not, it opens a SaveAs dialogue box and the file name is entered into the filename entry field, and the location has been selected based on the information in cell CU1. Cell CL1 contains the file name that should be used to save the file. The problem I am having is that when the macro runs, the SaveAs window appears as desired, the fiile name entry field is filled in with the data from cell CL1, the correct file path is listed at the top of the dialogue box which is the path from CU1, the correct file format (.xlsm - for macro enabled workbook) is selected below the file name entry field, but when the user selects the Save button at the bottom of the dialogue box, the file is not saved. The rest of the macro runs just fine, but I cannot get the SaveAs part to run. My code appears below. What am I doing wrong?

VBA Code:
Sub SAVE_AS_FILE()

    Dim FileFullName    As Variant
    Dim FileFilter      As String
    Dim FileInitial     As String

    FileInitial = Sheets("Block Tracking Multiple").Range("CU1").Value & Sheets("Block Tracking Multiple").Range("CL1").Value
    FileFilter = "Excel Workbook (*.xlsm), *.xlsm"
    
    ' Get location and filename to be used for saving

    If Sheets("Block Tracking Multiple").Range("I1").Value = "" Then
    
        MsgBox "A job number has not been entered." & vbCrLf & "Enter a job number to continue."
        Sheets("Block Tracking Multiple").Range("I1").Select
        Exit Sub
        
    Else
    
        If Sheets("Block Tracking Multiple").Range("DA1").Value = "" Then
                        
            FileFullName = Application.GetSaveAsFilename(InitialFileName:=FileInitial, FileFilter:=FileFilter)
            
            Sheets("Block Tracking Multiple").Range("DA1").Value = "YES"
            
            If FileFullName = False Then
                Sheets("Block Tracking Multiple").Range("DA1").ClearContents
            End If
            
        Else
            ThisWorkbook.Save
            
        End If
    End If
    
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

richh

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016
I could be wrong, but perhaps the code needs a ThisWorkbook.SaveAs FileFullName

VBA Code:
If Sheets("Block Tracking Multiple").Range("DA1").Value = "" Then
                        
            FileFullName = Application.GetSaveAsFilename(InitialFileName:=FileInitial, FileFilter:=FileFilter)
            
            Sheets("Block Tracking Multiple").Range("DA1").Value = "YES"
            
            If FileFullName = False Then
                Sheets("Block Tracking Multiple").Range("DA1").ClearContents
            Else
                ThisWorkbook.SaveAs FileFullName
            End If
 

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
528
Richh,

ThisWorkbook.SaveAs FileFullName

The above line did the trick. I figured it was something simple I was missing, just didn't know what. Thanks for the help.
 

Forum statistics

Threads
1,147,735
Messages
5,742,870
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top