want to save, print and close wb

[sakung]

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?


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

[raypayette]

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

[Martin Fishlock]

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?
>
>
> 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
>
>

[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.