want to save, print and close wb
 

i'm new in VBE and need some advice on my work. I would like to sav
invoice to another name and print that new workbook and then close i
by using macro.

On my code, run the Save As command to create a new copy
of the Workbook(pentagon0002.xls), then don't open up that workbook
just close it.
that will give me the error "Object Required". What else I need t
fix?
:confused:

Sub SaveandPrintout()

ActiveWindows.SelectSheets.PrintOut Copies:=1, Collate:=True

FileNum = ThisWorkbook.Sheets("Invoice").[B2].Value
FileNumStr = Format(FileNum, "0000")
ActiveWorkbook.SaveAs Filename:="c:\temp\test\pentagon" & FileNumStr
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWindows.Close SaveChanges:=False

End Su

--
sakun
-----------------------------------------------------------------------
sakung's Profile: http://www.excelforum.com/member.php...fo&userid=3723
View this thread: http://www.excelforum.com/showthread.php?threadid=57062

want to save, print and close wb
 

Hi Sakung,
Try
' define constants for clarity
Const filepath As String = "c:\temp\test\"
Const filename As String = "pentagon"
Const fileext As String = ".xls"

Sub SaveandPrintout()

FileNum = ThisWorkbook.Sheets("Invoice").[B2].Value
FileNumStr = Format(FileNum, "0000")
' save it
ActiveWorkbook.SaveAs _
filename:=filepath & filename & FileNumStr & fileext, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
' print it out
ActiveWorkbook.SelectedSheets.PrintOut _
Copies:=1, _
Collate:=True
' close it
ActiveWorkbook.Close SaveChanges:=False

End Sub

I changed the order as you said that you wanted it saved first, added some
constants and corrected your reference to the active workbook.

--
HTHs Martin


"sakung" wrote:


i'm new in VBE and need some advice on my work. I would like to save
invoice to another name and print that new workbook and then close it
by using macro.

On my code, run the Save As command to create a new copy
of the Workbook(pentagon0002.xls), then don't open up that workbook,
just close it.
that will give me the error "Object Required". What else I need to
fix?
:confused:

Sub SaveandPrintout()

ActiveWindows.SelectSheets.PrintOut Copies:=1, Collate:=True

FileNum = ThisWorkbook.Sheets("Invoice").[B2].Value
FileNumStr = Format(FileNum, "0000")
ActiveWorkbook.SaveAs Filename:="c:\temp\test\pentagon" & FileNumStr &
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWindows.Close SaveChanges:=False

End Sub


--
sakung
------------------------------------------------------------------------
sakung's Profile: http://www.excelforum.com/member.php...o&userid=37230
View this thread: http://www.excelforum.com/showthread...hreadid=570623

want to save, print and close wb
 

The best recommendation for you is to use the macro recorder by clickin
Tools, Macros, New Macro...
You mimic what you want done, then
close it when finished and examine the code

--
raypayett

-----------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...fo&userid=2956
View this thread: http://www.excelforum.com/showthread.php?threadid=57062

want to save, print and close wb
 

Thank you to Martin Fishlock and raypayette

Your code is working good. Thank you very much.

However, I need the old workbook (pentagon0001.xls) still open, only
close the new workbook(pentagon0002.xls) that just created.

Where can I type "Workbooks.open"?
I try to put it before end sub, It doesn't working.


--
sakung
------------------------------------------------------------------------
sakung's Profile: http://www.excelforum.com/member.php...o&userid=37230
View this thread: http://www.excelforum.com/showthread...hreadid=570623