Print Tiles for Limited Pages

[amkkhan]

I have an excel sheet of 15 pages. All I want to is print a header of 7 rows(I am using "Print Tiles" option) on first 10 pages and rest of the 5 pages should be without header.

So the main question is that Is it possible that I can use print tiles option for limited number of pages.

 

[John_w]

Try this macro. It clears the Print Area, sets the Title rows to rows 1:7 on the active sheet and prints pages 1-10. Then it clears the Title rows and sets the Print Area to after page 10 and prints the whole Print Area, which should print pages 11-15 without Title rows.

Public Sub Print_Sheet()

    Dim saveView As XlWindowView
    Dim repeatRows As String
    Dim titlesLastPage As Long
    Dim startPrintRow As Long
    Dim parts As Variant
   
    repeatRows = "$1:$7"
    titlesLastPage = 10
    
    saveView = ActiveWindow.View
    ActiveWindow.View = xlPageBreakPreview
    
    With ActiveSheet
                
        'Clear print area and set rows to repeat at top of each page
        
        .PageSetup.PrintArea = ""
        .PageSetup.PrintTitleRows = repeatRows
        
        'Calculate start row number of print area for page 11 to last page.  This is the row number of the 10th page break plus the number of title rows.
        'Because PrintTitleRows has been set above, the calculated row accounts for the repeated title rows on pages 1 to 10.
        
        startPrintRow = .Range(.HPageBreaks(titlesLastPage).Location).Row + Range(repeatRows).Rows.Count - 1
                
        'Print pages 1 to 10 with the title rows
        
        .PrintOut From:=1, To:=titlesLastPage, Copies:=1, Collate:=True, IgnorePrintAreas:=False

        'Clear title rows and set print area from page 11 to last page
        
        parts = Split(.UsedRange.Address, ":")
        .PageSetup.PrintTitleRows = ""
        .PageSetup.PrintArea = Left(parts(0), InStrRev(parts(0), "$")) & startPrintRow & ":" & parts(1)
                
        'Print all pages in the print area
        
        .PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        
        'Clear print area
        
        .PageSetup.PrintArea = ""
        
    End With
    
    ActiveWindow.View = saveView

End Sub