+ Reply to Thread
Results 1 to 2 of 2

Need Page Break on Change in Data in a Column

Hybrid View

  1. #1
    Registered User
    Join Date
    07-08-2013
    Location
    East Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    1

    Need Page Break on Change in Data in a Column

    I need some help getting printed output to page break when the value in a sorted column changes. My spreadsheet is a basic list where one column identifies a responsible organization. I need the output to page break when the responsible organization changes. Any help is appreciated.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Need Page Break on Change in Data in a Column

    Welcome to the forum.

    Try this and see if it does as you need - basic code, no error handling.
    Row-1 is assumed to be a header. The code must go into a standard module.
    1. Alt + F11 to open the VB Editor
    2. Menu > Insert > Module (not class module)
    3. Copy and paste code into code window
    4. Adjust sheet and/or range references if needed
    5. Alt + Q to close editor
    6. Alt + F8 to open the Macro Dialog (be sure the target worksheet is active before running the macro
    Option Explicit 
     
    Sub Set_PageBreaks() 
         
        Dim lastrow As Long, c As Range 
         
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row 
         
        Application.ScreenUpdating = False 
         
        ActiveSheet.ResetAllPageBreaks 
         
        For Each c In Range("A2:A" & lastrow) 
            If c.Offset(1, 0).Value <> c.Value And c.Offset(1, 0) <> "" Then 
                c.Offset(1, 0).PageBreak = xlPageBreakManual 
            End If 
        Next c 
         
        Application.ScreenUpdating = True 
         
    End Sub
    Or, just looping visible cells after advance filtering for unique values
    Option Explicit
    
    Sub Insert_Hbreaks()
    
        Dim c As Range, lastrow As Long, startrow As Long
        
        Application.ScreenUpdating = False
        
        With Sheet1
        
            .ResetAllPageBreaks
            .AutoFilterMode = False
            
            lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
            startrow = WorksheetFunction.CountIf(.Range("A2:A" & lastrow), .Range("A2").Value) + 1
            
            .Range("A1:A" & lastrow).AdvancedFilter xlFilterInPlace, , , True
            
            For Each c In .Range("A" & startrow & ":A" & lastrow).SpecialCells(12)
                Range("A" & c.Offset(1, 0).Row - 1).PageBreak = xlPageBreakManual
            Next c
            
            .ShowAllData
        End With
        
        Application.ScreenUpdating = True
    
    End Sub
    Last edited by Palmetto; 07-08-2013 at 08:08 PM.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1