PDA

View Full Version : [SOLVED] VBA code to set print preferences



Stickers
11-22-2019, 11:26 PM
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.

jolivanes
11-22-2019, 11:39 PM
When you used the macro recorder, what did you get? Remove the stuff you do not need.
You can use that with a loop for the sheets you want to use it on.

Stickers
11-22-2019, 11:45 PM
Hi, unfortunately I'm not advanced enough to create my own loops and I can't figure out from the recorder what to use. Would you be so kind to write one for me :yes

jolivanes
11-23-2019, 12:11 AM
Courtesy.
Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked.
You could have multiple people working on the same problem which would be a waste of time.

BTW, your statement "I can't figure what to use" does not tell us what you want, does it.
Run the macro recorder and show it here with the mentioning of what you want.

Stickers
11-23-2019, 12:31 AM
Sorry about that, all I need is narrow margins, landscape and A3 for all the sheets in the workbook.

See recorded code below.


Sub printer() Sheets(Array("Last Mth Processed", "This Mth Processed", "Last Qtr Processed", _
"This Qtr Processed", "Last Mth Submitted", "This Mth Submitted", _
"Last Qtr Submitted", "This Qtr Submitted", "KPI 14 Management", "All Notices")). _
Select
Sheets("Last Mth Processed").Activate
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA5
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Sheets("Last Mth Processed").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
End Sub

Stickers
11-23-2019, 12:52 AM
I think this is what I need but how do I make it work on all the sheets.....or do I have to do it for all the sheets individually?


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

Stickers
11-23-2019, 02:46 AM
The problem has been solved......I was activating an individual sheet, removed that and it now does all sheets.

jolivanes
11-24-2019, 08:31 PM
You say that it is solved. Good
In case you have to do something on all sheets except some, this would be one example on how to tackle that.

Sub Stickers()
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet3" 'Excluded Sheets
Case Else


'Here the code for the rest of the sheets


End Select
Next ws
End Sub


And to work on all sheets without exceptions

Sub All_Sheets()
Dim i As Long
For i = 1 To ActiveWorkbook.Sheets.Count
With Sheets(i).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
Next i
End Sub

Stickers
11-24-2019, 11:33 PM
The above didn't actually solve it anyway....for anybody looking at this thread later, the actual code is below. I had to remove Workbook and insert Application for it work in Personal.

And thank you @jolivanes, your code is even better.


Private Sub PrintSetup()
Dim ws As Worksheet
'For Each ws In ThisWorkbook.Worksheets ‘for Workbook
For Each ws In Application.Worksheets ‘for Personal

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 = xlPaperA4
.Zoom = 100
End With
Application.PrintCommunication = True
Next ws
End Sub

jolivanes
11-26-2019, 06:39 PM
The difference between "ThisWorkbook" and "ActiveWorkbook" refers to the workbook that is currently in the active window, whereas "ThisWorkbooK" refers to the workbook where the code is actually running from.

jolivanes
11-26-2019, 07:23 PM
If this is your post also, please make sure that in the future you'll let each site know that you are cross posting and copy the url into your post.
https://www.ozgrid.com/forum/index.php?thread/1226180-how-to-set-printing-preferences/ (https://www.ozgrid.com/forum/index.php?thread/1226180-how-to-set-printing-preferences/)

Stickers
11-26-2019, 07:33 PM
OK, will do...thanks for your help.