Save print settings so they will be the same on any instance of excel, not just the current sheet.

[jamiemarie]

Good Afternoon,
I have a question that I thought a simple Google search would answer. It seems however, that I am not that good at figuring out how to word what I want in order to get the correct answer. So I'm back here, and hopefully one of you experts will know what I'm trying to ask.

How do i change the print settings in excel and make it where the setting is the same no matter what workbook I open? Like, every time I open excel I want the same print setting. Where I work, we use the same settings for 99% of what I print in excel, but I can only seem to get the settings to stick for one sheet. However, I have to run reports in our data entry system and open the reports in excel then change the print settings every single time. It's really annoying having to do this 10 to 15 times a day. I am trying to do this on the excel program level, as we have other programs that need different printers/settings such as a label printer that I don't want to mess with. If for example the label printer settings get changed, IT would not be happy with me at all! I'm also hoping to not use a macro if possible, but I understand if that's my only option.

Hopefully this all makes sense and someone knows how to help. Please let me know if I need to clarify any ramblings. Thank you!

 

[jamiemarie]

Default : Booneville ZDesigner ZT410-203dpi ZPL on Ne07:
Need to use: TUPLEX02 on Ne00:

Got it, sorry about that!

 

[Trevor G]

Thank you. What I have done here is to look at the recording you did and take out parts that shouldn't be needed. I have also set it up so that all worksheets will have the same setting. Can you please try this on a copy of a workbook that has more than a single sheet before the next stage to make sure this works. I have added a msgbox to confirm it has completed the task.

Copy the code into VBA and into a new Module sheet.

Sub WorkBookPageSetup()
' PrintSettings Macro
' TupeloX2, One Sided, Landscape, Narrow Margins, All Columns on One Page

'From the recorded macro now lets set each worksheet to have the same settings
'My method is to declare the worksheet and then use a loop
'To go from one worksheet to the next until the end of the workbook
Dim ws As Worksheet
'Switching off various application controls to make everything faster
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With
For Each ws In Worksheets
    ws.Activate
    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)
'        .PrintQuality = 600
        .PaperSize = xlPaperLetter
        .Orientation = xlLandscape
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .FitToPagesWide = 1
    End With
  Application.PrintCommunication = True
Next ws
'Switching the application controls back on
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With
MsgBox "Each worksheet pagesetup has been adjusted", vbInformation, "Page Setup"

End Sub

 

[jamiemarie]

Thank you for all your work on this! I'm getting Run time Error 1004. Unable to set the PaperSize Propery of the Page setup class and debug is highlighting

.PaperSize = xlPaperLetter

.

 

[Trevor G]

Can you comment this line out, add an apostrophe at the beginning of the line. As it will turn the line Yellow you would have to reset the code if it is still highlighted in yellow. Reset is the Blue button in the toolbar within the VBA screen. Then please try it again. I am wondering if this a setting you can't use with the Label Printer?

 

[jamiemarie]

Can you comment this line out, add an apostrophe at the beginning of the line. As it will turn the line Yellow you would have to reset the code if it is still highlighted in yellow. Reset is the Blue button in the toolbar within the VBA screen. Then please try it again. I am wondering if this a setting you can't use with the Label Printer?

It worked! Message box came up, I checked all sheets and they all have the same setting. The only thing not working is the "Print on One Side", It's still showing "Print on Both Sides". If that's all I have to change manually, that's fine, like I said, the recorder wouldn't even pick it up. Again, for the millionth time, thank you so much!

 

[Trevor G]

The print on One Side is a printer setting rather than in Excel I believe. Just have to figure that out know.

Do you want the MsgBox to keep popping up, if yes leave everything if no then comment out the beginning of the line with an apostrophe.

Regarding the printer swapping can you test if this works

Sub ChangePrinter()
Application.ActivePrinter ="TUPLEX02 on Ne00:"
End Sub

Then swap it back

Sub SwapBack()
Application.ActivePrinter ="Booneville ZDesigner ZT410-203dpi ZPL on Ne07:"
End Sub

 

[jamiemarie]

The print on One Side is a printer setting rather than in Excel I believe. Just have to figure that out know.

Do you want the MsgBox to keep popping up, if yes leave everything if no then comment out the beginning of the line with an apostrophe.

Regarding the printer swapping can you test if this works

Sub ChangePrinter()
Application.ActivePrinter ="TUPLEX02 on Ne00:"
End Sub

Then swap it back

Sub SwapBack()
Application.ActivePrinter ="Booneville ZDesigner ZT410-203dpi ZPL on Ne07:"
End Sub

Both worked!

 

[Trevor G]

Ok that's good news. If you try this code does it display the Print Dialog box. Only use it with changing the printer to the one you want for single sided, and if it does would you be able to live with it so you can select single sided before it goes through and prints everything, like a quick safety check?

Sub showPrint()
Application.Dialogs(xlDialogPrint).Show
End Sub

 

[jamiemarie]

It does bring up the Print Dialog box and yes, that's completely fine for getting single sided. Thank you!

 

[Trevor G]

So we just need to put it all together and get it into your Personal Macro Workbook. Have you used the Personal Workbook before (It is normally hidden when you open Excel)?