Set print titles rows without using absolute reference

[KnoxBear832]

Anyone know how to set Print Title Rows that may vary from worksheet to worksheet?

Example Worksheet:

22	Terms:	NE…
23	Due Date:	09…
24	VAT/Tax ID Number:	20…
25

<tbody>
</tbody>

In this example, Rows 1 thru 25 should be the print titles, but this data may be up or down a few rows on future versions of the workbook.

So, I did a find on "VAT", selected the next cell down, then defined the active row as "EndRowNumber". Then tried to set the Print Title Rows as 1:EndRowNumber, which returned an error. (Unable to set the PrintTitleRows propery of the PageSetup class).

Any ideas or suggestions would be greatly appreciated!!

Cells.Find(What:="VAT", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Selection.Offset(1, 0).Select
    Dim EndRowNumber As Integer
    EndRowNumber = ActiveCell.Row
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "1:EndRowNumber"
        .PrintTitleColumns = ""
    End With

 

[Danerida]

Your EndRowNumber shouldn't be inside the double quotes. Also, you don't need to activate and select things to achieve this. You can do it like this:

Sub test()


Dim EndRowNumber As Integer


EndRowNumber = Cells.Find(What:="VAT", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Offset(1, 0).Row
   
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "1:" & EndRowNumber
        .PrintTitleColumns = ""
    End With


End Sub

 

[KnoxBear832]

Thank you Danerida. For some reason that sets the Print Row starting on the row with the active cell. I will just select "A1" and that should solve the problem. Thanks for pointing me in the right direction!

 

[Danerida]

Strange, my test gave me EndRowNumber = 25. Do you have "VAT" in anywhere in the first row? That would explain your result. You could try changing the Find to "VAT/Tax"