Contents
Let’s assume that you carefully set up the page settings for a beautiful printout of a table on one of the sheets in your Excel workbook. We printed it out, turned the sheet from the printer in our hands, rejoiced at the successful placement … and realized that you actually have 10 (or 110) sheets, i.e. you will have to repeat all the print settings many, many more times.
Actually, it’s not that bad 🙂
How should have been done
If you need to configure the same page settings for several sheets at once, then it is enough to first select them by holding Ctrl (sheets not in a row) or Shift (sheets in a row) and then set the print settings on the tab Page layout (Page Layout). For clarity, when several sheets are selected at once, a mark will be displayed in the title bar of the Excel window [Group]:
Don’t forget to deselect the group of sheets afterwards by clicking on any unselected sheet, as Many Excel features are temporarily unavailable in sheet group selection mode.
How to copy page settings from one sheet to others
If you have already made the necessary settings on one of the sheets, then most of them can be easily transferred to any other selected sheets of the book. To do this, first open the sample sheet, and then select also with Ctrl or Shift other sheets where you want to copy the print settings. Then go to the tab Page layout (Page Layout) and click on the arrow in the lower right corner of the group Page settings (Page Setup):
The Page Setup dialog box will open, displaying all the settings for the first selected sample sheet. Without changing anything, just click OK – and all settings will be applied to all selected sheets.
Print area and through rows-columns
The only thing that is not carried over from the page settings in the previous way is the print area and through rows / columns for printing on each page. They can be copied using a small macro. Open the Visual Basic Editor tab Developer – Visual Basic or with a keyboard shortcut Alt + F11, insert a new empty module via the menu Insert – Module and copy the code there:
Sub Copy_PrintArea_And_PrintTitles() Dim ws As Worksheet 'go through all the selected sheets and copy the settings to them from the current sheet .PageSetup.PrintTitleRows ' Through Rows ws.PageSetup.PrintTitleColumns = ActiveSheet.PageSetup.PrintTitleColumns ' Through Columns Next ws End Sub
Close the editor and return to Excel. Open the sample sheet and select (with Ctrl or Shift) other sheets. Run our macro with a keyboard shortcut Alt + F8 or using the button Macros tab developer (Developer — Macros).
- What are macros, how to create, copy and run them
- Saving workbook sheets as separate files
- Assembling sheets from different books into one