Sharing an Excel workbook with a group also means being involved with different printers, different PCs and different user requirements and expectations. This is nowhere more apparent then when it comes to printing a worksheet. Different users obviously have different PCs and may have different printers, so the printed results can vary from one user to another. In addition, different users may change the print ranges in what is produced from a worksheet.
If you are responsible for a particular worksheet, you may want to somehow protect the various print settings you've established so that they aren't garbled by other users. Perhaps the easiest way to do this is to save your print settings in a macro, and then run that macro every time the workbook is closed. In that way, the settings can be changed back to the "defaults" you specify, without worry that users will mess them all up.
For instance, the following macro shows how you can set all the print settings for a particular print job:
Sub Auto_Close() With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(1) .RightMargin = Application.InchesToPoints(1) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 99 .PrintErrors = xlPrintErrorsDisplayed .PrintArea = "MyPrintArea" .PrintTitleRows = "" .PrintTitleColumns = "" End With End Sub
To make the macro work for your particular needs, simply modify the settings to match whatever your requirements are.
Of course, when someone else opens your workbook, the macro may be disabled automatically or they may see a notification that there are macros in it. If they choose to disable the macros, then your default-setting macro won't run when the workbook is closed. The solution, of course, is for you to open the workbook, enable the macros, and then close the workbook. This runs the macro and your settings are again restored as you want them.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11599) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Protecting Print Settings.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel allows you to print out information in either portrait or landscape orientation, but what if you need both types of ...
Discover MoreWhen you print a worksheet, Excel allows you to also print handy gridlines for the worksheet. If you want to include the ...
Discover MoreNeed to print your worksheet on a non-standard paper size? Excel is rather limited in printing to such papers, and here ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-03-30 10:20:34
J. Woolley
@Arthur Menu
You can replace ActiveSheet with something like Worksheets("Sheet1") or Worksheets(N) for the Nth worksheet in tab order.
2022-03-29 17:30:23
Arthur Menu
I see in the auto close() example macro the line "With ActiveSheet.PageSetup". Is it possible to replace "With ActiveSheet" with the name of a sheet that is not the active sheet when closing the workbook so that the macro will run on that sheet before closing?
2017-04-03 14:02:48
Graham
Rather than manually creating this macro, which can introduce errors or omissions, there is an easy alternative.
1) Start recording a macro
2) Go into Page Setup (no need to make any changes)
3) Exit Page Setup
4) Stop recording the macro
This should create a macro containing ALL of the current print settings. I have found this to work from Excel 97 to Excel 2010.
If it does not work, then whilst recording the macro, try changing a single parameter and immediately change it back to its original value.
2017-03-18 17:10:03
David Gray
Another option is to store the settings in custom document properties, write a generic macro that reads and applies them, and save the macro as an add-in. This option makes it possible to apply them to any workbook, without embedding macros in it. Applying the settings can then be just a hot key away.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments