Writing an "insert page break" macro

I'm currently trying to write a macro using vbasic for a product list but I'm having a lot of difficulty with the code . I'd like to insert a page break AFTER the product code changes (the product code starts with letters and then has numbers after it) in terms of the starting letters (e.g. product ppx01 ppx06 *pagebreak* tty03

Can someone pls help me?

Thx.

Hi there,

Insert the following VBA code into the "ThisWorkbook" module of your workbook:

Option Explicit


Const strSheetName  As String = "Sheet1"
Const strProductCol As String = "A"
Const intFirstRow   As Integer = 4


Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim intLastRow  As Integer
    Dim i           As Integer

    intLastRow = ThisWorkbook.Sheets(strSheetName). _
                              Range(strProductCol & intFirstRow). _
                              Cells(1).End(xlDown).Row

    For i = intFirstRow To intLastRow
        If Sheets(strSheetName).Range(strProductCol & i).Value <> _
           Sheets(strSheetName).Range(strProductCol & i + 1).Value Then
               ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range(strProductCol & i + 1)
        End If
    Next i

End Sub

This will insert a page break after every row where the product code changes.

The parameters you need to set to suit your own workbook are:

Const strSheetName  As String = "Sheet1"
Const strProductCol As String = "A"
Const intFirstRow   As Integer = 4

strSheetName is the name of the worksheet which contains the product code data

strProductCol is the letter of the column which contains the product codes

intFirstRow is the row which contains the first product code - you may have blank rows, title rows etc. where page breaks should not be inserted.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

Thank you very much for your help. It worked perfectly!