Automatic setting of Print area of all the datas on a sheet

[PRADEEPB270]

Hi Hans,

Request you to please provide me a VBA code working ( Macro ) whose complete the 'Automatic setting of Print area of all the datas on a sheet and same on all the worksheet in a workbook with the follwing references only:-

Page:-

Orientation-Portrait
Adjust to-Not less than 80%
Fit to-According to 80% or more.Page preferably,1 to 2 or according to 'Adjust to'.
Paper Size-A4

Margin:-

Left-0.60
Top-0.20
Header-0
Right-0
Bottom-0
Footer-0

Center on page:-
'Tick' on Horizontally

For more clarification,please refer my attach file.

Can it be possible through a macro?

[Rudi]

Hi Pradeep,

See if this works for you...

Printing.xlsm

[PRADEEPB270]

Working fine.But,each sheet ,to press ALT+F8 and run every time.Can it be possible that press ALT+F8 once only and set all the sheets at once?And the macro is very slow.Please,if possible to faster than earlier.

[HansV]

Try this if you're using Excel 2010 or later:

Code: Select all

Sub SetupPrintout()
    Dim wsh As Worksheet
    Application.ScreenUpdating = False
    Application.PrintCommunication = False
    For Each wsh In Worksheets
        With wsh.PageSetup
            .LeftMargin = Application.CentimetersToPoints(0.6)
            .RightMargin = 0
            .TopMargin = Application.CentimetersToPoints(0.2)
            .BottomMargin = 0
            .HeaderMargin = 0
            .FooterMargin = 0
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With
    Next wsh
    Application.PrintCommunication = True
    Application.ScreenUpdating = True
End Sub

[Rudi]

Oops...my error that it did not run on all sheets...
As for the speed...page setup macros are notoriously slow. I'll see if there is a way to speed it up more...

For now...try this version.

Edited: Ah... I see Hans uploaded a new version.... TX.

[PRADEEPB270]

Yes,Hans sir it is the perfect macro and is running well without any problem.Many-2 thanks to you sir for nice cooperation and given learning concept to me.