want to save, print and close wb

[sakung]

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?


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

 

[Guest]

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.

 

[raypayette]

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

 

[sakung]

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.