Macro to 'save as' in a specific folder

Hi all,

I have spreadsheets that are completed 2x daily, then a report is run from them (creating another sheet in the spreadsheet). Once this is done, I want to assign a macro to a button to pop up a 'save as' window, prompting them to save it in a folder of my choosing.

Workbook attached - TIA

Use:

Application.Dialogs(xlDialogSaveAs).Show ("Path & FileName here")

Application.Dialogs(xlDialogSaveAs).Show ("Z:\FireOperations\Shift Reports\Unprocessed\" & FileName & ".xlsx")

If you want change the macro to the following - it will default then to "Z:\FireOperations\Shift Reports\Unprocessed\"

Sub saveFileNoMacro()
ChDir "Z:\FireOperations\Shift Reports\Unprocessed"
Sheets("ShiftSummary").Copy
fullFileName = Application.GetSaveAsFilename("DefaultFilename.xlsx", _
    "Excel files (*.xlsx),*.xlsx", 1, "Save File As")
If fullFileName = False Then
MsgBox "File will not be saved"
Else
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fullFileName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
End If
End Sub

I have added a button to your worksheet and attached the following code - this will copy the report into a new workbook which you then can save in directory of your choice and name it what ever you want.

Sub saveFileNoMacro()
Sheets("ShiftSummary").Copy
fullFileName = Application.GetSaveAsFilename("DefaultFilename.xlsx", _
    "Excel files (*.xlsx),*.xlsx", 1, "Save File As")
If fullFileName = False Then
MsgBox "File will not be saved"
Else
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fullFileName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
End If
End Sub

You simply browse to the folder - when the file save dialogue comes up you can select any folder you want. When you hit the save button you can use the Windows explorer box just like you normally would when saving a file in excel. It may default to a directory however you can change the directory to where ever you want. You can also make the filename what ever you want.

See my post just before your last post - I added in a change directory command so it will default to "Z:\FireOperations\Shift Reports\Unprocessed\"

Change it to

Sub saveFileNoMacro()
ChDir "Z:\FireOperations\Shift Reports\Unprocessed"
Sheets("ShiftSummary").Copy
fullFileName = Application.GetSaveAsFilename("DefaultFilename.xls", _
    "Excel files (*.xls),*.xls", 1, "Save File As")
If fullFileName = False Then
MsgBox "File will not be saved"
Else
Application.DisplayAlerts = False
 ActiveWorkbook.SaveAs Filename:= _
        fullFileName, FileFormat _
        :=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
Application.DisplayAlerts = True
End If
End Sub

Nuts - spoke too soon...I am trying to use this is Excel 2003

Sorry - my fault - this will work with Excel 2003 - I have place a button on your workbook and attached the macro - Changed your code so the active page after running the report is A-1 - you can change that back if you want.

Sub saveReportFile()
ChDir "Z:\FireOperations\Shift Reports\Unprocessed"
Sheets("ShiftSummary").Copy
fullFileName = Application.GetSaveAsFilename("DefaultFilename.xls", _
    "Excel files (*.xls),*.xls", 1, "Save File As")
If fullFileName = False Then
MsgBox "File will not be saved"
Else
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fullFileName, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
Application.DisplayAlerts = True
End If
End Sub

If you want to close the report automatically after saving just add in after the save a Close command.

ActiveWorkbook.SaveAs Filename:=fullFileName, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
ActiveWorkbook.Close False ' This is the line to add if you want to automatically close the generated report after it is saved.
Application.DisplayAlerts = True
End If
End Sub