Creating page breaks on data cell changes & using cell data from page breaks in header

[Rickywilson89]

Hello,

I'm newly registered here but have used the site for many years and it has been extremely useful.

My current issue...

I have a current VBA Code that creates a page break when the reference number in column A changes.

I have quite a lot of columns in this spreadsheet and it's hard to print without it being small, so I wanted to use cell data from column a, b, c & d to form part of the header and these columns can then be hidden.

Is this possible?

Many thanks in advance

 

[Worf]

Welcome

Can you post your code?

Dim pag(), s$, ws As Worksheet

Sub DifferentH()
Dim i%
Set ws = ActiveSheet
PageAddress2 True                                                           ' get addresses
Columns("a:d").Hidden = 1                                                   ' unwanted columns
For i = 1 To UBound(pag)                                                    ' all pages
    With ws.PageSetup
        .PrintArea = pag(i).Address
        .LeftHeader = pag(i).Cells(1, 1) & "/" & pag(i).Cells(1, 2) & _
        "/" & pag(i).Cells(1, 3) & "/" & pag(i).Cells(1, 4)                 ' unique header
    End With
    ws.PrintPreview
Next
Application.ScreenUpdating = 1
ws.PageSetup.PrintArea = ""
End Sub

Sub PageAddress2(colorcode As Boolean)
Dim c%, v%, h%, cln%, rw%, hgth%, wth%, i%
c = 1: s = ""
ActiveWindow.View = xlPageBreakPreview
ws.PageSetup.PrintArea = ""
ws.PageSetup.PrintArea = ws.UsedRange.Address                               ' force page break recalculation
ReDim Preserve pag(1 To (ws.VPageBreaks.Count + 1) * (ws.HPageBreaks.Count + 1))     'all pages on that sheet
For v = 0 To ws.VPageBreaks.Count
    For h = 0 To ws.HPageBreaks.Count
        If v = ws.VPageBreaks.Count Then
            wth = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
        Else
            wth = ws.VPageBreaks(v + 1).Location.Column - 1
        End If
        If h = ws.HPageBreaks.Count Then
            hgth = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
        Else
            hgth = ws.HPageBreaks(h + 1).Location.Row - 1
        End If
        If v = 0 Then
            cln = 1
        Else
            cln = ws.VPageBreaks(v).Location.Column
        End If
        If h = 0 Then
            rw = 1
        Else
            rw = ws.HPageBreaks(h).Location.Row
        End If
        Set pag(c) = ws.Range(ws.Cells(rw, cln).Address & ":" & ws.Cells(hgth, wth).Address)
        s = s & pag(c).Address & vbLf
        If colorcode Then pag(c).Interior.Color = RGB(CInt(250 * Rnd), CInt(250 * Rnd), CInt(250 * Rnd))
        c = c + 1
    Next
Next
End Sub
' ***********