Insert a Page Break before a specific cell value

ALISONH613

New Member
Joined
Sep 26, 2014
Messages
2
I am having a hard time trying to set a page break before a cell value. I have tried two codes (see below) and am getting an error with a number 400 for the first one and no macros to run on the second. I am a novice and need to split over 20000 excel rows to print (I want page breaks by class- then each page would be a different class period, class name and include student scores to be distributed to teachers)

Option ExplicitSub PageBreaks() Dim c As Range Dim FirstAddress AsStringDim Search AsStringDim Prompt AsStringDim Title AsString Prompt = "What do you want to search for?" Title = "Search Term Input" Search = InputBox(Prompt, Title) If Search = "" Then Exit SubEndIfWith ActiveSheet.UsedRange Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) IfNot c IsNothingThen FirstAddress = c.Address Do ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c Set c = .FindNext(c) LoopWhileNot c IsNothingAnd c.Address <> FirstAddress EndIfEnd WithEnd Sub

Dim Search As String Search = "student" With ActiveSheet.UsedRange Set c = .Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True) If Not c Is Nothing Then FirstAddress = c.Address Do ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> FirstAddress End If End
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Code:
Sub InsertPageBreaks()
    
    'exit if active sheet is not a worksheet
    If TypeName(ActiveSheet) <> _
        "Worksheet" Then Exit Sub
    
    Dim find_text As Variant
    
    'prompt for string to find
    find_text = Application.InputBox( _
        Prompt:="Find what:", _
        Title:="Find", _
        Type:=2)
    
    'exit if dialog box cancelled
    If find_text = False Then Exit Sub
    
    'reset all page breaks
    ActiveSheet.Cells.PageBreak = xlPageBreakNone
    
    Dim num As Long
    Dim cell As Range
    
    'loop through cells and insert page break
    'whenever cell value equals string to find
    num = 0
    For Each cell In ActiveSheet.UsedRange
        If LCase(cell.Value) = LCase(find_text) Then
            num = num + 1
            cell.PageBreak = xlPageBreakManual
        End If
    Next cell
    
    'prompt if string not found
    If num = 0 Then
        MsgBox Prompt:="Microsoft Excel cannot " & _
            "find the data you're searching for."
    End If

End Sub
 

ALISONH613

New Member
Joined
Sep 26, 2014
Messages
2
Thank you!! Unfortunately, it s putting a vertical page break before the keyword and I need a horizontal page break. I also seeing the error message "Type mismatch"

.
.

Code:
Sub InsertPageBreaks()
    
    'exit if active sheet is not a worksheet
    If TypeName(ActiveSheet) <> _
        "Worksheet" Then Exit Sub
    
    Dim find_text As Variant
    
    'prompt for string to find
    find_text = Application.InputBox( _
        Prompt:="Find what:", _
        Title:="Find", _
        Type:=2)
    
    'exit if dialog box cancelled
    If find_text = False Then Exit Sub
    
    'reset all page breaks
    ActiveSheet.Cells.PageBreak = xlPageBreakNone
    
    Dim num As Long
    Dim cell As Range
    
    'loop through cells and insert page break
    'whenever cell value equals string to find
    num = 0
    For Each cell In ActiveSheet.UsedRange
        If LCase(cell.Value) = LCase(find_text) Then
            num = num + 1
            cell.PageBreak = xlPageBreakManual
        End If
    Next cell
    
    'prompt if string not found
    If num = 0 Then
        MsgBox Prompt:="Microsoft Excel cannot " & _
            "find the data you're searching for."
    End If

End Sub
 

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
Thank you!! Unfortunately, it s putting a vertical page break before the keyword and I need a horizontal page break. I also seeing the error message "Type mismatch"


Change this line...

From:

cell.PageBreak = xlPageBreakManual

To:

ActiveSheet.HPageBreaks.Add Before:=cell


Also, which line is giving you the type mismatch error?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,292
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top