Set print titles rows without using absolute reference

KnoxBear832

New Member
Joined
Aug 25, 2014
Messages
4
Anyone know how to set Print Title Rows that may vary from worksheet to worksheet?

Example Worksheet:

22Terms:NE…
23Due Date:09…
24VAT/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!!


Code:
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

Board Regular
Joined
Aug 25, 2014
Messages
152
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:
Code:
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

New Member
Joined
Aug 25, 2014
Messages
4
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

Board Regular
Joined
Aug 25, 2014
Messages
152
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"
 

Forum statistics

Threads
1,085,990
Messages
5,387,136
Members
402,045
Latest member
Hidalgo

Some videos you may like

This Week's Hot Topics

Top