Page Break macro in Excel 2

[TulsaJeff]

I have a spreadsheet containing Bills of Material with 6 columns and over 1000 rows of data. The data in columns A and B are duplicated down several rows since they are the parent item and description... the components, UM, and other data are in the other columns. Some bills have 3 components some may have 10 but at every change in Parent (column A) I would like a page break.


Basically, I need a macro that will create a page break at every change in column A. I have recorded macros and then tweaked the macro but this looks like something that would need to be written from scratch using some sort of IF statement... Help anyone?

Regards,
TulsaJeff

 

[Crystalyzer]

Why don't you insert subtotals based on column "A" and check the box to "Page break between groups"?

No macro required!

 

[AnotherHiggins]

TulsaJeff,

Assuming you have Headers in Row 1, this should do it:

Range("a3").Activate
Do Until ActiveCell.Row = ActiveSheet.UsedRange.Rows.Count + 1
    If ActiveCell = ActiveCell.Offset(-1) Then
        ActiveCell.Offset(1).Activate
    Else
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        ActiveCell.Offset(1).Activate
    End If
Loop
End Sub

This code assumes that column A is populated for each record in the report, if that is not the case, please post back.

Hope that helps you out,
John