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]

Yes, I have a couple macros saved to my personal workbook already.

 

[Trevor G]

Great so my thoughts are we approach it like this:

In the personal workbook add a new Module sheet to do the following:
First step - Code to change the default printer
Second Step - Code to set the pagesetup for all worksheets in the active workbook (I'm not sure if you will open the workbook first)
Third Step - Display the print dialogue box (Manual check for single print)
Forth Step - Change printer back to default printer
Fifth Step - Close active workbook (we haven't spoken about that but I can add it in if needed) Don't save the changes

 

[jamiemarie]

Ok, so this is what I have.

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




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



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



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

The Print Dialog box does not come up now. Everything else is working.

 

[Trevor G]

They have to be combined rather than separate sub routines. Try this:
What about the close workbook as mentioned or do you want to leave it open?

Sub CompleteTasks()
Application.ActivePrinter = "TUPLEX02 on Ne00:"
' 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"
Application.Dialogs(xlDialogPrint).Show
'Swap back to the Default Printer
Application.ActivePrinter = "Booneville ZDesigner ZT410-203dpi ZPL on Ne07:"
End Sub

 

[jamiemarie]

Tried that and it went perfectly! And yes, I would like to leave the workbook open.

 

[Trevor G]

Great news that you have a solution and happy to help you. Thank you for letting me know.