VBA code to set print preferences

[571202]

Hi, I need a VBA code to set the print preferences for all the worksheets in my workbook (in Personal) to narrow margins, landscape and A3.......hopefully with one sub to do all the sheets at once. Cheers.

 

[James006]

Hi,

As long as your printing preferences are valid within your workbook ... there is no need to store your macro in Personal ...

 

[571202]

Hi, a report is sent to several people and I'm installing the macro on their computers so they can just run the macro after they open the reports.

Yes.....I know....it scary that I'm installing macros on other peoples computers but I'm the best of a hopeless mob

 

[James006]

Fair enough ...

You should post your macro ...

 

[571202]

The problem has been solved......I was activating an individual sheet, removed that and it now does all sheets.

Private Sub PrintSetup()
        With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Orientation = xlLandscape
        .PaperSize = xlPaperA3
        .Zoom = 100
        End With
    Application.PrintCommunication = True
End Sub

 

[James006]

You are welcome

 

[YasserKhalil]

For all worksheets in one shot

Private Sub PrintSetup()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        With ws.PageSetup
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .Orientation = xlLandscape
            .PaperSize = xlPaperA3
            .Zoom = 100
        End With
        Application.PrintCommunication = True
    Next ws
End Sub

 

[571202]

Hi, @YasserKhalil , thanks for your reply. Unfortunately it didn't work for my macro. I inserted it as the last sub....is that correct. I am using Personal, not a Workbook if that makes a difference. What do you think the problem might be? Cheers.

 

[571202]

Got it.....changed ThisWorkbook to Application

Thanks very much.