Pass Row 2, Column A Cell to Header After Each Page Break

[Riccosuave]

Okay, I found this bit of code from a post back in 2010, and I am hoping that somebody can please help me figure out what kind of variable "hb" should be, and what it should be pointing to because the original person that posted this code never included them at all. I know this code was intended to do what I want, but I cannot for the life of me figure out how to complete it. I have included a link to the original post, and I am really hoping somebody can help me here.

Original Post: excel vba change header at each page

What I am ultimately trying to do is change the header that is printed to the cell value of the second row of Column A every time there is a page break.

So, for example:
Pages (1-10)
Cell Value (A2) = Trees
So Right Header on Page (1-10) Should = Trees

Page Break

Pages (11-20)
Cell Value (Value of Second Row in Column A after the Page Break) = Buildings
So Header on Pages (11-20) Should = Buildings

Here is the code that I have. All I really need is to know what "hb" should be defined as and pointing to I guess? I hope I am asking this right.

Sub Insert_Headers()
Dim hb As ? '(I need to know what this should be)
hb = ? '(I need to know what this should be too)

'The first page
ActiveSheet.PageSetup.RightHeader = Cells(ActiveSheet.HPageBreaks(1).Location.Row - 2, 1).Value
counter = 1

'to get the other pages and manipulate them
For Each hb In ActiveSheet.HPageBreaks
counter = counter + 1
ActiveSheet.PageSetup.RightHeader = Cells(hb.Location.Row, 2).Value
Next hb
End Sub

 

[Alex Blakenburg]

See if this is what you had in mind.

Based on my understanding:-

• For Page 1 the header comes from A2
• After that the header changes every 10 pages eg 11,21,31 and this is controlled by the variable NoOfPages
• When it changes if the first row of page 11 is 471 then it puts the value in A472 (471 + 1 ie 2nd row of page 11) into the header

Sub Insert_Headers()
    Dim hb As HPageBreak
    Dim sht As Worksheet
    Dim counter As Long
    Dim NoOfPages As Long
    Dim PageNo As Long
    
    Set sht = ActiveSheet
    NoOfPages = 10          ' Page Header changes every 10 pages
    
    ActiveWindow.View = xlPageBreakPreview
    'The first page - Header comes from A2
    sht.PageSetup.RightHeader = sht.Cells(2, "A").Value
    
    PageNo = 1
    'get the other pages and manipulate them
    For counter = 1 To sht.HPageBreaks.Count
        'counter = 1 is break before page 2
        PageNo = PageNo + 1
        'Heading only changes every x pages where is is NoOfPages
        If PageNo Mod NoOfPages = 1 Then
            ' 1st Page break is already page 2
            Set hb = sht.HPageBreaks(counter)
            ' Header comes from Column A
            sht.PageSetup.RightHeader = sht.Cells(hb.Location.Row + 1, "A").Value
        End If
    Next counter

    ActiveWindow.View = xlNormalView
End Sub

 

[Riccosuave]

Alex,

First, I just wanted to say I really appreciate you taking the time to leave me a response to my question. Unfortunately I think I did not do the best job with my initial explanation, and I can see where the confusion arose in the example that I framed.

The header will not be changing every 10 pages, or on any "set" page rotation. The header needs to change every time the text value in Column A changes. There are going to be text value changes in Column A at sporadic intervals, and I need the header to match whatever that new value is in Column A. So essentially the header value on each page should be the second value in Column A of each printed page so I am not putting the Table Header for Column A in the Header.

Hopefully this makes a little bit more sense. Thanks for any additional assistance that you can provide. Again, much appreciated.

 

[Alex Blakenburg]

How will the code know to use a new header ?
eg
1) is column A blank except for the headers ?
2) does column A have the category value on every row and the heading changes when it changes

 

[Riccosuave]

Alex,

The code should know to change the header based upon the second example that you gave. Each row in column A has a category value in it, and when that value changes then the page header should change with it.

 

[Alex Blakenburg]

Do you want each new category to start on a new page ?

 

[Alex Blakenburg]

This is not pretty but see if this is what you had in mind.
If it does what you want and you no longer want to see the preview screen on each section break,
change the True to False on the 2 lines that look like this.
sht.PrintOut From:=sectPageFirst, To:=pgNo - 1, Preview:=True
Note: this appears twice in the below

PS: If you are just testing just hit close print preview on the print preview screen (the X in the top right works too)

Sub InsertPagebreakAndPageHdr()

'Declaring variables
    Dim ColNo As Long
    Dim RowNo
    Dim LastRow As Long
    Dim sht As Worksheet
    Dim hb As HPageBreak
    Dim pgNo As Long
    Dim i As Long
    Dim sectPageFirst As Long
    
    Set sht = ActiveSheet
    
    'Clear existing page breaks
    sht.ResetAllPageBreaks
    
    ColNo = 1
    
    'Getting row number of last cell
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
    'Insert additional page breaks for each change of category
    For RowNo = 2 To LastRow
    
        'Comparing values in cell of two consecutive rows for the specified column
        If Cells(RowNo, ColNo).Value <> Cells(RowNo - 1, ColNo).Value Then
            
            If RowNo > 10 Then
                'Inserting page break - but not on finding the 1st category
                sht.HPageBreaks.Add Before:=Cells(RowNo, ColNo)
            End If
            ' Header comes from Column A
            sht.PageSetup.RightHeader = sht.Cells(RowNo, "A").Value
        End If
        
    Next RowNo
    
    pgNo = 1
    
    ' 1st Category
    sht.PageSetup.RightHeader = sht.Cells(2, "A").Value
    sectPageFirst = 1
    ' loop through page header
    For i = 1 To sht.HPageBreaks.Count
        pgNo = pgNo + 1
        Set hb = sht.HPageBreaks(i)
    
        'Comparing values in cell of two consecutive rows for the specified column
        If Cells(hb.Location.Row, ColNo).Value <> Cells(hb.Location.Row - 1, ColNo).Value Then
            'Print previous section
            sht.PrintOut From:=sectPageFirst, To:=pgNo - 1, Preview:=True
            
            ' Header comes from Column A
            sht.PageSetup.RightHeader = sht.Cells(hb.Location.Row, "A").Value
            sectPageFirst = pgNo
            ' Select statement to overcome known bug in program
            ' http://support.microsoft.com/kb/210663
            sht.Cells(LastRow, "A").Select
        End If
    Next i
    
    ' Print last section
    sht.PrintOut From:=sectPageFirst, To:=pgNo, Preview:=True
    
    ' Clean Up
    sht.PageSetup.RightHeader = ""
    sht.Cells(1, "A").Select

End Sub