How to repeat title rows on top in certain pages when printing?

In some cases when working with Excel, you may need to print selected page ranges (for example, pages 1 to 6) with the title rows displayed at the top, while ensuring that the remaining pages are printed without the repeating title rows. However, the built-in Excel 'Rows to repeat at top' feature applies title rows to all pages throughout the printout, lacking the flexibility to specify different settings for different page ranges. This tutorial will guide you step by step on how to repeat title rows only on certain pages during printing and print other pages without title rows, addressing a common limitation in Excel's default printing options.

Note: The methods in this tutorial are tested in Excel 2021. If you are using different Excel versions, some steps or dialog layouts may vary slightly. If you encounter unexpected behavior, please refer to your version's help resources or check for interface updates.

This approach allows you to manually divide your print job so only certain pages include title rows at the top, making long printouts more readable for selected sections such as management or data analysis reports. It's particularly suitable when different audiences require different formatting for portions of a worksheet. However, this method involves several manual setup steps and page break management, so please follow carefully to avoid printing errors.

1. Open the worksheet you wish to print. On the toolbar, click Page Layout > Print Titles. This menu provides access to settings for repeating title rows in the printed output.

2. In the Page Setup dialog, locate the Rows to repeat at top box and click the indicator button next to it. Then select the row(s) in your worksheet that serve as your title rows (often the first row). Click OK to confirm.

Tip: Selecting the correct title rows avoids confusion in your printout. Generally, select all rows that contain column headers or table titles for readability.

3. Go to File > Print. Within the Print pane, use the page navigation arrows on the right preview pane to view the last page you intend to print with the title rows (in this example, page 6). Make a note of the last line printed on that page for accurate range division.

Tip: For large worksheets, zoom the print preview to check carefully where page breaks appear. Accurate observation here will help prevent overlapping or missing data when splitting pages.

4. In the Print area, enter the specific page range you want to print with title rows (for example, Pages: 1-6), then click Print. Your selected pages will include the repeated title rows.

Note: Be sure the page ranges you set match those observed in the previous preview step to avoid printing undesired sections.

5. Return to your worksheet. Locate the row immediately after the last line that appeared on the last printed page in the previous step. Go to Page Layout > Breaks > Insert Page Break to manually insert a page break at the correct position.

By inserting a page break, you ensure that subsequent pages start exactly where you want and that Excel treats pages 1-6 and pages 7 onward as separate print segments.

6. Again click Page Layout > Print Titles. In the Page Setup dialog, clear the Rows to repeat at top box. Click OK to confirm the removal.

Tip: Double-check the removal of repeated rows in this dialog to ensure title rows won't appear on subsequent pages. The preview can assist with confirmation.

7. Go to File > Print again, type the page numbers you want to print without title rows (e.g., page 7 to the last page in your worksheet) into the Pages box, then click Print to finish.

Now your print job will result in pages 1-6 displaying the title rows and the rest of your document printing without them.

Applicable Scenarios: This solution is best suited when you need differentiated print formatting for project reports, data submissions, or when different user groups require distinct page header layouts. It provides full control but requires careful manual management of page breaks, title row settings, and print ranges.

Pros: Offers precise page-by-page control. No need for advanced coding or external plugins.
Cons: Requires multiple manual actions—may be time-consuming for very large documents. Risk of misaligned breaks if pages shift as data is edited.

Practical Tips: Before starting, consider saving a backup copy of your worksheet and check your print scaling settings to avoid unwanted resizing that could affect pagination.
Common Issues: If page breaks seem incorrect after changes, revisit the print preview and reset page breaks if necessary.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!

If you frequently need to print selected page ranges with custom title row settings, automating the process using a VBA macro can save time and minimize errors. This is especially effective for periodic reporting, large datasets split into sections, or when page divisions change often and manual setup is cumbersome.

Using VBA, you can programmatically set the title rows for one range, print that range, clear the titles, and print another range, all without manual dialog setup or page break insertion. This streamlines repetitive print jobs, ensures consistent formatting, and reduces risk of manual mistakes.

Applicable Scenarios: Ideal for power users, office administrators, and anyone managing regular printing of multi-section reports or data sheets, as well as for workbook templates shared across teams.
Pros: Automates multi-stage printing, consistent results, batch processing support.
Cons: Requires access to Developer tab and VBA editor. Macro security must be considered for shared files.

Follow these steps to use the VBA macro:

1. Click Developer on the ribbon. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. Then choose Visual Basic to open the VBA editor window. In the VBA editor, click Insert > Module to add a new code module.

2. Copy and paste the following VBA code into the module window:

Sub PrintTitlesSelectedPages()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim PageStart1 As Integer, PageEnd1 As Integer
    Dim PageStart2 As Integer, PageEnd2 As Integer
    Dim TitleRows As String
    Dim xTitleId As String
    xTitleId = "KutoolsforExcel"

    On Error Resume Next
    TitleRows = Application.InputBox("Enter title rows (e.g., $1:$1):", xTitleId, "$1:$1", Type:=2)
    If TitleRows = "" Then Exit Sub

    PageStart1 = Application.InputBox("First print range: start page number?", xTitleId, 1, Type:=1)
    PageEnd1 = Application.InputBox("First print range: end page number?", xTitleId, PageStart1, Type:=1)

    ws.PageSetup.PrintTitleRows = TitleRows
    ws.PrintOut From:=PageStart1, To:=PageEnd1
    ws.PageSetup.PrintTitleRows = ""

    PageStart2 = Application.InputBox("Second print range: start page without title rows?", xTitleId, PageEnd1 + 1, Type:=1)
    PageEnd2 = Application.InputBox("Second print range: end page without title rows?", xTitleId, ws.PageSetup.Pages.Count, Type:=1)
    ws.PrintOut From:=PageStart2, To:=PageEnd2

    ws.PageSetup.PrintTitleRows = TitleRows ' Restore original setting if needed
End Sub

3. To execute the macro, press F5 or click the button in the VBA editor toolbar. A series of dialog boxes will prompt you for:

• The title row reference (e.g., $1:$1)
• The start and end page for printing with title rows
• The start and end page for printing without title rows

The macro will automatically set the title rows, print the first specified page range with titles, clear the titles, and print the next range without titles. No manual setup, page break, or dialog navigation is needed.

Parameter Explanation:

• TitleRows: Specify the row(s) (e.g., $1:$1 for row 1) for your titles.
• PageStart1/PageEnd1: Enter numbers such as 1-6 for the initial range.
• PageStart2/PageEnd2: Enter ranges such as 7-12 for the next section if your worksheet spans more pages.

Error Reminders and Troubleshooting:

• If you enter an invalid row reference or page number, the macro will exit without printing—double-check your input format.
• Page numbering is determined by Excel's print preview pagination; if data changes, actual page numbers may shift.
• To ensure correct page division, inspect your worksheet in Print Preview before running the macro.
• After printing, you can restore the original title row settings if needed.

---

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

Read More...Full Features 30-Day Free Trial...Purchase... 

---

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!

Read More...Full Features 30-Day Free Trial...Purchase...