Inserting page breaks based on grouped value changes

[Jammer]

Hi.

I have a simple list that contains several columns. For example, Teacher, Period and Subject are possile column headers. Under each header are teachers listed by period and subject. Everytime the teacher and period (grouped) changes I would like to automatically insert a page break. For example, Teacher Smith period 2, Teacher Smith period 3, Teacher Jones period 2, Teacher Jones Period 5, Teacher Green Period 5, Teacher Green Period 5. Thus, it should not just change when it recognizes a new teacher but a new teacher-period group. How should this be accomplished? Any help would be appreciated. I can send a file if necessary.

 

[Yogi Anand]

Hi Jammer:

Please look at the following simulation and clarify where do you intend to have the Page_Breaks inserted ...

y030524h1.xls
	A	B	C	D
1	Teacher	Period	Subject
2	Smith	2
3	Smith	3
4	Jones	2
5	Jones	5
6	Green	5
7	Green	5
Sheet7

 

[Jammer]

Hi.

I need to have the page breaks inserted every time there is a change in both the TEACHER name AND the PERIOD, so that every page printed would contain a teacher's name and period.

Example:

Jones, Period 2 (on one page)
Jones, Period 3 (on one page)

Not Jones Period 2 and Jones Period 3 on the same page.

 

[Yogi Anand]

Hi Jammer:

If I have understood you correctly, one of the ways to do what you want is by using the following code ...

Sub yInsertPageBreakAtPeriodInColumnB()
    For Each Cell In [A3:A7]
        If Left(Cell(1, 2), 6) = "Period" Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cell
        End If
    Next Cell
End Sub

I have written the macro to be operated on the range A3:A7 as shown in the following simulation. Please adjust the range to suit your own specific requirement. In the simulation the red line shows the page-break inserted by the code:[RANGE=cls:xl2bb-100][XR][XH=cs:5]y030525h1.xls[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][/XR][XR][XH]1[/XH][XD=h:l]Teacher[/XD][XD=h:l]Period[/XD][XD=h:l]Subject[/XD][XD=h:r][/XD][/XR][XR][XH]2[/XH][XD=h:l]Jones[/XD][XD=h:l]Period2[/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]3[/XH][XD=h:l]Jones[/XD][XD=h:l]Period3[/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]4[/XH][XD=h:l]Jones[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]5[/XH][XD=h:l]hockey[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]6[/XH][XD=h:l]football[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]7[/XH][XD=h:l]Smith[/XD][XD=h:l]Period5[/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH=cs:5][RANGE][XR][XD]Sheet13[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

Would this do?

 

[Jammer]

I'm sorry. This doesn't work and the diagram does not look correct. Each teacher and his corresponding period should be listed on its own page.

Jones, Period 2, and all its other fields should be totally separate from
Jones, Period 3
Smith, Period 1, Student Johnny & Smith, Period 1, Student Jane (same page)

Black, Period 2
Black, Period 3

Please also explain to me, how to basically say this in a macro. If the contents of A1 and A2 concatenated DO NOT equal the contents of the cells below them (B1 AND B2 concatenated) in a pre-sorted list, then put a page break, otherwise leave it alone.

Thanks.

 

[arunjhalani]

Book4
	B	C	D	E
3	Teacher	Period	Teacher+Period	Subject
4	Smith	2	Smith2	A
5	Smith	3	Smith3	B
6	Jones	2	Jones2	C
7	Jones	5	Jones5	D
8	Green	5	Green5	A
9	Green	5	Green5	B
Sheet1

CREATE a new Column as UNION OF TEACHER&PERIOD.
Now create a pivot with Teacher&pivot as the first col & then Period & then Teacehr or whichever u want. Check the layout of Ist column, & on Field Check the Box of Insert Page break after new item.

 

[Jammer]

Thank you. Your options have worked using a pivot table. I appreciate your help very much everyone.