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

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,675
Office Version
  1. 2016
Platform
  1. Windows
A couple of questions:

Are you wanting to use a particular Printer on a network.
When you say settings but can only do it on one sheet, what are the settings you want.
If you could change the setting for one sheet have you tried recording a Macro to get all the settings.
Do you mean Page setup with things like Page Orientation, Headers & Footers

It is more than likely you may need VBA based on your answers.
 

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Are you wanting to use a particular Printer on a network.
Yes - Set on LabelPrinter now and I have to change it to MyDeskPrinter all of which are on a network.

When you say settings but can only do it on one sheet, what are the settings you want.
Do you mean Page setup with things like Page Orientation, Headers & Footers

Page setup is what I mean, so the steps I take are:
Change printer to MyDeskPrinter
Print One Sided
Landscape Orientation
Narrow Margins
Fit All Columns on One Page

If you could change the setting for one sheet have you tried recording a Macro to get all the settings
I have not, I didn't know if it would be possible, and with your followup questions I can see that using the term settings wasn't quite right and that's probably why I couldn't find an answer on google. I always think I'm being specific enough! Thank you for taking the time to send follow up questions.
 

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
The following is my recorded macro. Unfortunately I have been unable to make the change from "Print on both sides" to "Print one Side". Even when recording a macro with only that step, the macro ends up empty. The rest of it works perfectly though!

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

'
    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.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .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
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .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
        .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
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .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
        .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
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .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
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,675
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I am not on a network but this first snip of code should provide the default printers name. It will print the name to the Immediate Window. In the VBA screen Go to the View Menu and select Immediate Window. Add the code into a Module and run the code.

VBA Code:
Sub GetDefaultPrinterName()
Debug.Print Application.ActivePrinter 'Print the name of the printer to the immediate window
End Sub

You need to know the Printer name and it needs to be available to select.

You can record a macro to give you the code for setting up the worksheet which can then be adapted for all sheets and also when your using Excel.

Please look to record a macro to:
Print One Sided
Landscape Orientation
Narrow Margins
Fit All Columns on One Page

If you post back the code I can adapt it for your needs.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,675
Office Version
  1. 2016
Platform
  1. Windows
Thank you for posting the code. I was just writing a reply. I will look at the code and strip out the things it doesn't need.
 

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I am not on a network but this first snip of code should provide the default printers name. It will print the name to the Immediate Window. In the VBA screen Go to the View Menu and select Immediate Window. Add the code into a Module and run the code.

VBA Code:
Sub GetDefaultPrinterName()
Debug.Print Application.ActivePrinter 'Print the name of the printer to the immediate window
End Sub

You need to know the Printer name and it needs to be available to select.

You can record a macro to give you the code for setting up the worksheet which can then be adapted for all sheets and also when your using Excel.

Please look to record a macro to:
Print One Sided
Landscape Orientation
Narrow Margins
Fit All Columns on One Page

If you post back the code I can adapt it for your needs.
Please look to Post # 5 for my code so far. I am going to test the code you just gave for the printer, as I could not get that or the "Print one Sided" to record in my macro. Thank you again for your time on this.
 

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Thank you for posting the code. I was just writing a reply. I will look at the code and strip out the things it doesn't need.

Thank you! I tried your code to get the default printer by going to my VBA screen, View, View Immediate Window and adding the code to the box that comes up. Unfortunately it is giving me a compile error. Please refer to the posted screenshot, since I may have done something wrong!
 

Attachments

  • Compile Error.PNG
    Compile Error.PNG
    34.2 KB · Views: 1

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Just going into the printer settings on my computer, the printer that is the default right now is ZDesigner ZT410-203dpi ZPL. The printer I want to use is Lexmark MS820 Series XL.

When in excel these are the names given in the drop down list Booneville ZDesigner ZT410-203dpi ZPL and Tuplex02 respectively.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,675
Office Version
  1. 2016
Platform
  1. Windows
Thank you! I tried your code to get the default printer by going to my VBA screen, View, View Immediate Window and adding the code to the box that comes up. Unfortunately it is giving me a compile error. Please refer to the posted screenshot, since I may have done something wrong!
The code needs to go into a Module, please do this. In VBA select the Insert menu and Module, then paste the code in the Module not the Immediate Window. When you Run the code it will display the default Printer Name in the Immediate Window. Once you've done that can you go to the Front screen of Excel and manually change the default printer to the one you need to use. Then switch back to VBA and rerun the code it will give you both printer names which will be needed Copy both lines from the Immediate Window and Paste into your thread. I understand you have written there names down but the code may pick up something else to use as well.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,783
Messages
5,542,497
Members
410,557
Latest member
chidambaramseetha
Top