I have a Print macro button on my spreadsheet that sets the print area,
hides certain rows and prints.
Is there a bit of VB that will let me set up the save options much like
the 'Save As' option.
ie File name, and where to save.


--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=518959

Hi grahammal

You can use this GetSaveAsFilename

Example that use SaveCopyAs to save the activeworkbook

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
If fname = False Then Exit Sub
If Dir(fname) < "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"grahammal" wrote in message
...

I have a Print macro button on my spreadsheet that sets the print area,
hides certain rows and prints.
Is there a bit of VB that will let me set up the save options much like
the 'Save As' option.
ie File name, and where to save.


--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=518959

I have tailored your answer to my query to include the date and time.

Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("Book6 " & Date & " " & Time, _
fileFilter:="Excel Files (*.xls), *.xls")
If fname = False Then Exit Sub
If Dir(fname) < "" Then GoTo Again
Wb.SaveCopyAs fname

How do I get it to always save in a default location.
ie D:\My Documents\Excel


--
grahammal
------------------------------------------------------------------------
grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
View this thread: http://www.excelforum.com/showthread...hreadid=518959