Macro to save file and print document

A

abxy

ok, I need a to try to make a macro to save the active worksheet to m
C: drive with THE filename, (A3) SALES ORDERS

more specifically I need to have it save the filename as the content
of cell A3 then SALES ORDERS

Then I need it to print to worksheet.

How do I do this?

Thanks in advanc
 
M

Mike Fogleman

Sub SaveAs()
Dim rngA3

rngA3 = Sheet1.Range("A3").Value
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\" & rngA3 & " Sales orders.xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Modify sheet name and directory folders as needed.
Hope this gets you started, Mike
 
A

abxy

What if cell A3 is date? because excel isn't letting me save the fil
because of cell's date separators(/), I've tried changing the format o
the date in the cell, but Excel still interprets the date with
symbols, so i can't save it, becuase windows thinks the /'s are part o
a path nam
 
M

Mike Fogleman

this will handle a date and text or numbers: changes 1/13/2004 to 1_13_2004

Sub SaveAs()
Dim rngA3

rngA3 = Sheet1.Range("A3").Value
If IsDate(rngA3) = True Then
rngA3 = Replace(rngA3, "/", "_")
End If
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\" & rngA3 & " Sales orders.xls", _
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
 
Top