Results 1 to 7 of 7

Inserting page breaks based on grouped value changes

This is a discussion on Inserting page breaks based on grouped value changes within the Excel Questions forums, part of the Question Forums category; Hi. I have a simple list that contains several columns. For example, Teacher, Period and Subject are possile column headers. ...

  1. #1
    New Member
    Join Date
    Sep 2002
    Location
    Jammer
    Posts
    17

    Default Inserting page breaks based on grouped value changes

    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.


  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Inserting page breaks based on grouped value changes

    Hi Jammer:

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

    ******** ******************** ************************************************************************>
    Microsoft Excel - y030524h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    TeacherPeriodSubject*
    2
    Smith2**
    3
    Smith3**
    4
    Jones2**
    5
    Jones5**
    6
    Green5**
    7
    Green5**
    Sheet7*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    New Member
    Join Date
    Sep 2002
    Location
    Jammer
    Posts
    17

    Default Re: Inserting page breaks based on grouped value changes

    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.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Inserting page breaks based on grouped value changes

    Hi Jammer:

    If I have understood you correctly, one of the ways to do what you want is by using the following code ...
    [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:

    ******** ******************** ************************************************************************>
    Microsoft Excel - y030525h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    TeacherPeriodSubject*
    2
    JonesPeriod*2**
    3
    JonesPeriod*3**
    4
    Jones***
    5
    hockey***
    6
    football***
    7
    SmithPeriod*5**
    Sheet13*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Would this do?
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    New Member
    Join Date
    Sep 2002
    Location
    Jammer
    Posts
    17

    Default Re: Inserting page breaks based on grouped value changes

    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.

  6. #6
    Board Regular
    Join Date
    Oct 2002
    Location
    Kolkatta
    Posts
    121

    Default Re: Inserting page breaks based on grouped value changes

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000
    File Edit View Insert Options Tools Data Window Help About
    =

    B
    C
    D
    E
    3
    TeacherPeriodTeacher+PeriodSubject
    4
    Smith2Smith2A
    5
    Smith3Smith3B
    6
    Jones2Jones2C
    7
    Jones5Jones5D
    8
    Green5Green5A
    9
    Green5Green5B
    Sheet1*

    [HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    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.

  7. #7
    New Member
    Join Date
    Sep 2002
    Location
    Jammer
    Posts
    17

    Default Re: Inserting page breaks based on grouped value changes

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com