+ Reply to Thread
Results 1 to 5 of 5

Macro that inserts page breaks into a monthly report when name changes occur in column C

Hybrid View

  1. #1
    Registered User
    Join Date
    12-05-2022
    Location
    Boston
    MS-Off Ver
    Office 365
    Posts
    2

    Macro that inserts page breaks into a monthly report when name changes occur in column C

    The attached monthly report is exported from another system (non-MS) and will contain the names of 250-300 individuals. I want a macro that will automatically insert page breaks in column C when the name changes versus doing it manually myself. The size of the report would make manual insertion counter-productive.
    Attached Files Attached Files

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,310

    Re: Macro that inserts page breaks into a monthly report when name changes occur in column

    Possibly...
    Sub Test()
        Dim v As Variant
        v = Application.Transpose(Sheet1.UsedRange.Columns(3).Value)
        
        Dim i As Long
        With CreateObject("Scripting.Dictionary")
            For i = LBound(v) To UBound(v)
                If Not IsEmpty(v(i)) Then .Item(v(i)) = Empty
            Next i
            v = .keys
        End With
        
        Dim c As Range
        For i = 1 To UBound(v)
            Set c = Sheet1.UsedRange.Find(v(i))
            Sheet1.Rows(c.Row).PageBreak = xlPageBreakManual
        Next i
    End Sub

  3. #3
    Registered User
    Join Date
    12-05-2022
    Location
    Boston
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Macro that inserts page breaks into a monthly report when name changes occur in column

    Thank you for the response and help.

    The macro works to a certain extent. It inserts a page break BELOW the "Single restoration codes" heading (see attachment).
    Is there a way to have the page break inserted above the row so that the heading is included with the next student's name?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    3,032

    Re: Macro that inserts page breaks into a monthly report when name changes occur in column

    another approach

    Sub Test()
    Dim pn As Range
    Dim cn As Range
    Dim i As Long, lr As Long, r As Long
    
    With ActiveSheet
        On Error Resume Next
        lr = Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
        On Error GoTo 0
        If lr = 0 Then Exit Sub
    
        For i = lr To 2 Step -1
            If .Range("a" & i) = "" Then .Range("a" & i).EntireRow.Delete
        Next
    
        On Error Resume Next
        lr = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
        On Error GoTo 0
        
        If lr > 0 Then
            .ResetAllPageBreaks
            With .PageSetup
                .PrintArea = "A1:M" & lr
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = False
            End With
            For r = 5 To lr
                If .Range("A" & r).MergeArea.Cells.Count = 1 Then
                    Set cn = .Range("C" & r)
                    If Not pn Is Nothing Then
                        If cn.Value <> pn.Value Then
                            .HPageBreaks.Add before:=pn.Offset(1, 0)
                        End If
                    End If
                    Set pn = cn
                End If
            Next
        End If
    End With
    End Sub

  5. #5
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,310

    Re: Macro that inserts page breaks into a monthly report when name changes occur in column

    Possibly...
    Sub Test()
        Dim v As Variant
        v = Application.Transpose(Sheet1.UsedRange.Columns(3).Value)
        
        Dim i As Long
        With CreateObject("Scripting.Dictionary")
            For i = LBound(v) To UBound(v)
                If Not IsEmpty(v(i)) Then .Item(v(i)) = Empty
            Next i
            v = .keys
        End With
        
        Dim c As Range
        For i = 1 To UBound(v)
            Set c = Sheet1.UsedRange.Find(v(i))
            Sheet1.Rows(c.Row-1).PageBreak = xlPageBreakManual
        Next i
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Creating a Report to Be Printed - Page Breaks Not Cooperating
    By js0873 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-10-2022, 01:38 PM
  2. Macro to set Page Breaks
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-25-2021, 09:40 AM
  3. Macro that inserts page number in a specific cell.
    By mlcfexcel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2015, 08:14 AM
  4. printing in word inserts page breaks through my document
    By adante in forum Word Formatting & General
    Replies: 0
    Last Post: 11-13-2009, 03:51 AM
  5. Replies: 1
    Last Post: 03-15-2009, 06:08 PM
  6. Page Breaks Macro
    By Brian Matlack in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2006, 04:49 PM
  7. Macro page breaks on column value not row 1 - use value in footer?
    By misscrf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2005, 02:18 PM

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