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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,643
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top