PDA

View Full Version : Insert page break after each row that has a cell with a specified value



Fuzzy99
12-11-2006, 02:08 AM
Hi,

is there a macro which allow me to insert a page break below each row that has a cell with a specified value? Pls help...:help

Fuzzy99
12-11-2006, 02:10 AM
Hi,

I saw a macro which allows me to insert a page break above each row that has a cell with a specified value. Can this macro be modified to allow me to insert a page break below each row instead of above? Pls help...:help

OBP
12-11-2006, 03:32 AM
Hello Fuzzy, yes of course it can be modified.
Can you post a copy of the Macro Code on here for us to advise you what to change?

Charlize
12-11-2006, 04:06 AM
Something like this ?
Sub page_endings()
Dim i As Long 'how many times for pagebreak
Dim searchvalue_for_break_after 'value to do pagebreak
searchvalue_for_break_after = "break after"
'column A must be filled in with value break after
'example row 6, 12, 18, 24 whatever row you want
'will loop until empty row in column A
For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & i).Value = searchvalue_for_break_after Then
'will add a pagebreak after the row with value break after
ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Range("A" & i).Offset(1)
End If
Next i
End Sub
Charlize

Charlize
12-11-2006, 04:21 AM
If it's for a specified sheet you better use :
Sheets(1).HPageBreaks.Add before:=Range("A" & i).Offset(1)
If a different sheet is visible then it will still be sheet no 1 that will get the page breaks.

Charlize

Simon Lloyd
12-11-2006, 05:03 AM
This should give you the basics to work with for what you need
Sub Macro1()
If ActiveCell = "Fred" Then
ActiveWindow.SelectedSheets.HPageBreaks.Add before:=ActiveCell.Offset(1, 0)
End If
End Sub

Regards,
Simon

Charlize
12-11-2006, 05:07 AM
which thread are we going to use ?

Charlize

Charlize
12-11-2006, 05:08 AM
Or maybe this thread ?

Charlize

johnske
12-11-2006, 05:14 AM
Threads merged...

Simon Lloyd
12-11-2006, 10:54 AM
Glad you cleared that up Johnske.....saves on the confusion overload!

Regards,
Simon

Fuzzy99
12-12-2006, 02:18 AM
Hi OBP,

Thanks for the reply. The original macro code i have is as follow & I like to have the page break below the cell with specified value. Appreciate if you can help me to modify this.

Option Explicit

Sub PageBreaks()

Dim c As Range
Dim FirstAddress As String
Dim Search As String
Dim Prompt As String
Dim Title As String

Prompt = "What do you want to search for?"
Title = "Search Term Input"
Search = InputBox(Prompt, Title)
If Search = "" Then
Exit Sub
End If
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 With

End Sub

Thanks :)
Fuzzy

Charlize
12-12-2006, 02:47 AM
Hi OBP,

Thanks for the reply. The original macro code i have is as follow & I like to have the page break below the cell with specified value. Appreciate if you can help me to modify this.

Option Explicit

Sub PageBreaks()

Dim c As Range
Dim FirstAddress As String
Dim Search As String
Dim Prompt As String
Dim Title As String

Prompt = "What do you want to search for?"
Title = "Search Term Input"
Search = InputBox(Prompt, Title)
If Search = "" Then
Exit Sub
End If
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.offset(1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

End Sub

Thanks :)
Fuzzy
Add the red code to the one you already have.

Charlize

Simon Lloyd
12-12-2006, 02:49 AM
This does exactly what you need!


Sub PageBreaks()
Dim c As Range
Dim FirstAddress As String
Dim Search As String
Dim Prompt As String
Dim Title As String
Prompt = "What do you want to search for?"
Title = "Search Term Input"
Search = InputBox(Prompt, Title)
If Search = "" Then
Exit Sub
End If
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.Offset(1, 0)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub
Regards,
Simon

Fuzzy99
12-12-2006, 06:33 PM
Hi Charlize, Simon,

You are a life saver. Thanks heaps for your help.

Fuzzy:beerchug:

shrivallabha
03-29-2010, 10:19 AM
No necroposting this. The code helped me. You guys are still a life savers :)