Condition for Page Break

AndreyS. · Aug 2nd 2004

hi, i needed inserted condition for Page Break's ...
If inside cel's = 'Hello', the 'Page Break', must be embed.

for another help, very thank's !

:rock:

Blue_Hornet · Aug 2nd 2004

VBA Solution

Hi, Andrey, and welcome to the site.

I don't know of a way to do what you want through formatting. I do have a macro that will do it for you ...

This macro looks for the word "Hello" (by itself, if you want it to find "Hello, World" or "Hello, Bob" and treat them the same, the code would require modification) in the working cells in Column A:A and, when found, inserts a PageBreak at that point.

You can look up elsewhere on this site and find out how to paste code into a module.

Code

Sub InsertBreaks()
'   Insert PageBreaks in A:A wherever 'Hello' appears


    Application.ScreenUpdating = False
    ' Set the area to be worked
    Set Workrange = Columns("A:A").EntireColumn
    Set Workrange = Intersect(Workrange.Parent.UsedRange, Workrange)
    CellCount = Workrange.Count


    ' Remove existing PageBreaks from bottom to top
    For myBreaks = ActiveSheet.HPageBreaks.Count To 1 Step -1
        ActiveSheet.HPageBreaks(myBreaks).Delete
    Next myBreaks


    ' Insert PageBreaks at "Hello", starting at the top of 'WorkRange' _
        and working down to the bottom
    For myCounter = Workrange.Row To Workrange.Row + CellCount
        If Cells(myCounter, 1) = "Hello" Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cells(myCounter, 1)
        End If
    Next myCounter
    Application.ScreenUpdating = True
End Sub

Display More

Chris

AndreyS. · Aug 3rd 2004

Quote from Blue_Hornet
Hi, Andrey, and welcome to the site.

I don't know of a way to do what you want through formatting. I do have a macro that will do it for you ...

This macro looks for the word "Hello" (by itself, if you want it to find "Hello, World" or "Hello, Bob" and treat them the same, the code would require modification) in the working cells in Column A:A and, when found, inserts a PageBreak at that point.

You can look up elsewhere on this site and find out how to paste code into a module.
Code
Sub InsertBreaks()
'   Insert PageBreaks in A:A wherever 'Hello' appears


    Application.ScreenUpdating = False
    ' Set the area to be worked
    Set Workrange = Columns("A:A").EntireColumn
    Set Workrange = Intersect(Workrange.Parent.UsedRange, Workrange)
    CellCount = Workrange.Count


    ' Remove existing PageBreaks from bottom to top
    For myBreaks = ActiveSheet.HPageBreaks.Count To 1 Step -1
        ActiveSheet.HPageBreaks(myBreaks).Delete
    Next myBreaks


    ' Insert PageBreaks at "Hello", starting at the top of 'WorkRange' _
        and working down to the bottom
    For myCounter = Workrange.Row To Workrange.Row + CellCount
        If Cells(myCounter, 1) = "Hello" Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cells(myCounter, 1)
        End If
    Next myCounter
    Application.ScreenUpdating = True
End Sub
Display More
Chris
Display More

hi, my case is : for everything the word= " Register", needed insert a page break, what ? the document header, is initiate " Register", consider spaces...
treat the text document imported, format no appropriate ...

Blue_Hornet · Aug 3rd 2004

Hi, Andrey

This modified code addresses the 'Register' issue you mentioned. For any cell in A:A that contains the full word 'Register' (wherever it appears in the cell), there will be a page break inserted. (Notice that this won't insert a page break for 'registration', or 'registry', but it will insert a break for 'Register', 'register', 'REGISTER', 'registers', 'registering', etc.)

The code also resolves the issue you mailed me about, where an error message was generated because you had no existing page breaks installed. I should have tested for that. (And it's okay to point out errors in the public posting; other people may pick up code here from time to time, and if you already know it generates an error, they'd want to know also. I appreciate your apparent concern for my sensitivity in this regard.)

Chris

Code

Sub InsertBreaks()
'   Insert PageBreaks in A:A wherever 'Register' appears


    Application.ScreenUpdating = False
    ' Set the area to be worked (This section borrowed from J. Walkenbach.)
    Set WorkRange = Columns("A:A").EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count


    ' Remove existing PageBreaks from bottom to top
    On Error Resume Next
    For myBreaks = ActiveSheet.HPageBreaks.Count To 1 Step -1
        ActiveSheet.HPageBreaks(myBreaks).Delete
    Next myBreaks


    ' Insert PageBreaks at any cell in A:A containing "Hello", starting at the top of 'WorkRange'
    ' and working down to the bottom
    For myCounter = WorkRange.Row To WorkRange.Row + CellCount
        If Not (Application.IsErr(Application.Find("REGISTER", UCase(Cells(myCounter, 1))))) Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cells(myCounter, 1)
        End If
    Next myCounter
    Application.ScreenUpdating = True
End Sub

Display More

Condition for Page Break

Participate now!

Share