Save as that pulls data from Worksheet

[jackson1990]

Hey everyone!

Was wondering if someone could help me out with a mystery. I have a macro that asks the user to save their Excel. I was looking if I could default the Save As Text to be the text from say H16 on the Active Sheet. So instead of taking the default name of the workbook they have currently (say Workbook1), it would default to whatever the text is in H16. I have some code now, but couldn't quite connect how I would get it to default as that. Here's the code.

Sub Update()

Application.ScreenUpdating = False


Dim wsheet As Worksheet


Sheets("Sheet1").Select


    ActiveWorkbook.RefreshAll
    DoEvents
    
If Worksheets("Sheet1").Range("D14") = Worksheets("Sheet1").Range("F14") Then
    MsgBox "Update Complete", vbInformation + vbOKOnly
    MsgBox "Please save the file in the location you will keep it permanently. If you do not, certain features will not work.", vbExclamation + vbOKOnly


    wsn = Application.GetSaveAsFilename(FileFilter:= _
    "Excel Files (*.xlsm), *.xlsm")
    
    If wsn <> False Then
        ActiveWorkbook.SaveAs _
        Filename:="test", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled
        
    ActiveSheet.CommandButton1.Enabled = True
    End If
End Sub

Thanks everyone for taking a look!

 

[JLGWhiz]

Something like this could be used.

If Worksheets("Sheet1").Range("D14") = Worksheets("Sheet1").Range("F14") Then
    MsgBox "Update Complete", vbInformation + vbOKOnly
    If Sheets("Sheet1").Range("H16") = "" Then
        MsgBox "The SaveAs destination is not entered in cell H16, You must manually perform the SaveAs action.", vbExclamation + vbOKOnly
    Else
        ActiveWorkbook.SaveAs(ActiveSheet.Range("H16").Value)
    End If

Assuming sheet one is the active sheet, this would check cell H16. If it is empty, it would advise the user to create the file path and name for saving the active workbook. If H16 is NOT empty, then it would automatically save the activeworkbook to the path and filename designated in H16.

 

[jackson1990]

This worked flawlessly! Thanks JLGWhiz!

 

[JLGWhiz]

This worked flawlessly! Thanks JLGWhiz!

You're welcome,
regardes, JLG