Page break problem!
Page break problem!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Page break problem!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi,

    I need to insert a page break when the value in colum F changes. It's a huge list and I'm sure there is an easier way!

    Thanks in advance
    Matt

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matt

    Have a look at the Subtotals.. feature under Data.

    If this is no good you will need VBA using the pagebreak Object.



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks.... I've had a look at that, but I don't know how to force the page break at "each change in". I also thought of doing it as a pivot table then splitting out the report page by page - but there are just to many changes.... can you give me a hit with the page break object?
    Matt

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry....

    Can someone expand on this for me - I think it needs somesort of offset statement but I don't know how to do it.

    Sub PageBreakAtChange()
    Columns("E:E").Select
    Selection.ColumnDifferences(ActiveCell).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    End Sub

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matt


    This is off the cuff, but should help


    Sub SumCols()
    Dim rCells As Range
    ActiveSheet.DisplayAutomaticPageBreaks = False
    For Each rCells In Range("F2", Range("F65536").End(xlUp))
    If rCells <> rCells.Offset(-1, 0) Then
    rCells.EntireRow.PageBreak = xlPageBreakManual
    End If
    Next rCells
    End Sub



  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks - can you give me some comments on how this one works, just so I know for next time... thanks

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    Sub SumCols()
    Dim rCells As Range
    'Clear all current page breaks
    ActiveSheet.DisplayAutomaticPageBreaks = False
    'Loop through all cells in the range("F2:F)
    For Each rCells In Range("F2", Range("F65536").End(xlUp))

    If rCells <> rCells.Offset(-1, 0) Then 'Change in value
    'Add page break
    rCells.EntireRow.PageBreak = xlPageBreakManual
    End If
    Next rCells
    End Sub


    To use it Push Alt+F11, go to Insert>Module and paste it in. Push Alt+Q then Alt+F8, then select the macro name and Click "Run". Just make sure you are on the correct sheet.



User Tag List

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