How to repeatedly print titles in multiple sheets?
To print the header row at top repeatedly in each page is usually used in our working, but have you ever tried to print title rows repeatedly in multiple sheets? In this article, I have two easy ways to handle this question in Excel.
Print titles repeatedly in multiple worksheets with VBA
Print titles repeatedly in multiple worksheets with Kutools for Excel
Print titles repeatedly in multiple worksheets with VBA
There is no built-in feature to repeatedly print titles in multiple sheets, but you can use a macro to handle it.
1. Activate the first sheet in your workbook, click the anchor at the bottom-right corner of Page Setup group on the Page Layout tab. See screenshot:
2. In the Page Setup dialog, click Sheet tab, select the title row you want to repeatedly print from Rows to repeat at top, and click OK. See screenshot:
3. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window, click Insert > Module to create a new Module.
4. Then paste below code to the blank script, and press F5 to run the code.
VBA: Repeatedly print titles in multiple sheets
Sub SetAttributes()
'UpdatebyExtendoffice20170810
Dim xSheet As Worksheet
Dim xSheetOne As Worksheet
Dim xSheets As Sheets
Set xSheets = ActiveWorkbook.Worksheets
Set xSheetOne = xSheets(1)
For Each xSheet In xSheets
xSheet.PageSetup.PrintTitleRows = xSheetOne.PageSetup.PrintTitleRows
Next xSheet
End
After applying the VBA, the top row in each sheet will be repeated at top of corresponding sheet while printing.
Print titles repeatedly in multiple worksheets with Kutools for Excel
If you have installed Kutools for Excel, you can apply its Copy Page Setup to quickly copy all or specific page setup from one sheet to other sheets in a workbook.
| Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. |
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select one sheet, for instance, select Sheet1, and click the anchor at the bottom-right corner of Page Setup group on the Page Layout tab. See screenshot:
2. In the Page Setup dialog, click Sheet tab, and select the titles you want to repeatedly print from Rows to repeat at top. See screenshot:
3. Click OK, then click Kutools Plus > Printing > Copy Page Setup. See screenshot：
4. In the Copy Page Setup dialog, check the sheet names you want to apply the same page setup in Copy to list, then check Rows to repeat at top in the Options list. See screenshot:
5. Click Ok. Now the top rows have been repeated at the top across multiple sheets while printing.
Demo
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
