Hello all

I have a macro to create an invoice. At the end the worksheet is copied to a new worksheet and automatically saved. But when I want to print this new worksheet it doesn't fit on 1 page. When I use the fit-to-page option in the print-options box it's working. But I want to do this in the macro. So after I copied the sheet, I would like to give a command to set fit-to-page

This is the syntax I use to copy
Sheets("Invoice").Selec
Window1$ = ActiveWindow.Captio
Range("A1:M50").Selec
Selection.Cop
Workbooks.Ad
Window2$ = ActiveWindow.Captio
ActiveSheet.DropDowns.Add(144, 105.75, 248, 15.75).Selec
ActiveSheet.Past
Windows(Window1$).Activat
Range("B2").Selec
Application.CutCopyMode = Fals
Windows(Window2$).Activat

Anyone an idea how I can do this

Thanks in advance for any help

Marco.

Hi Marco

Try this

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveSheet.PrintOut


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


"Marco" wrote in message ...
Hello all,

I have a macro to create an invoice. At the end the worksheet is copied to a new worksheet and automatically saved. But when I
want to print this new worksheet it doesn't fit on 1 page. When I use the fit-to-page option in the print-options box it's working.
But I want to do this in the macro. So after I copied the sheet, I would like to give a command to set fit-to-page.

This is the syntax I use to copy:
Sheets("Invoice").Select
Window1$ = ActiveWindow.Caption
Range("A1:M50").Select
Selection.Copy
Workbooks.Add
Window2$ = ActiveWindow.Caption
ActiveSheet.DropDowns.Add(144, 105.75, 248, 15.75).Select
ActiveSheet.Paste
Windows(Window1$).Activate
Range("B2").Select
Application.CutCopyMode = False
Windows(Window2$).Activate

Anyone an idea how I can do this?

Thanks in advance for any help.

Marco.

Try this:


With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

If it doesnt work correctly (above macro is for
Excel2000), just record your own macro with the macro
recorder.

regards
hans


-----Original Message-----
Hello all,

I have a macro to create an invoice. At the end the
worksheet is copied to a new worksheet and automatically
saved. But when I want to print this new worksheet it
doesn't fit on 1 page. When I use the fit-to-page option
in the print-options box it's working. But I want to do
this in the macro. So after I copied the sheet, I would
like to give a command to set fit-to-page.

This is the syntax I use to copy:
Sheets("Invoice").Select
Window1$ = ActiveWindow.Caption
Range("A1:M50").Select
Selection.Copy
Workbooks.Add
Window2$ = ActiveWindow.Caption
ActiveSheet.DropDowns.Add(144, 105.75, 248,
15.75).Select
ActiveSheet.Paste
Windows(Window1$).Activate
Range("B2").Select
Application.CutCopyMode = False
Windows(Window2$).Activate

Anyone an idea how I can do this?

Thanks in advance for any help.

Marco.
.

Add this to the end:

ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1

The Macro Recorder is our friend!



--
Message posted from http://www.ExcelForum.com