Pam in TX
06-06-2007, 07:16 PM
I have 3 macros that work fine seperately but when I put them together it only pulls on the 3rd part (page break at section change)
I hope it is ok to post this...... My sheet is already sorted by Teacher, Course and Section....... What I am wanting is to place a page break whenever either the Teacher Course or Section columns change.... Here is the macro:
Sub TeacherCourseSectionPageBreaks()
'
' CreatePageBreaks Macro
'
'Searches for the heading teacher and moves down one row
Cells.Find(What:="teacher", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
'Searches for the heading Course and moves down one row
Cells.Find(What:="Course", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
'Searches for the heading Section and moves down one row
Cells.Find(What:="Section", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
'Defines Variables
Dim S1 As String
Dim S2 As String
Top:
'Determines value of active cell and stores as S1 (string 1)then moves down one row
S1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
'Determines value of active cell and stores as S2 (string 2)
S2 = ActiveCell.Value
Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
If S1 = S2 Then
GoTo Top:
End If
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
GoTo Top:
Loop
Range("A1").Select
End Sub
Thanks in advance for your help......
I hope it is ok to post this...... My sheet is already sorted by Teacher, Course and Section....... What I am wanting is to place a page break whenever either the Teacher Course or Section columns change.... Here is the macro:
Sub TeacherCourseSectionPageBreaks()
'
' CreatePageBreaks Macro
'
'Searches for the heading teacher and moves down one row
Cells.Find(What:="teacher", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
'Searches for the heading Course and moves down one row
Cells.Find(What:="Course", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
'Searches for the heading Section and moves down one row
Cells.Find(What:="Section", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
'Defines Variables
Dim S1 As String
Dim S2 As String
Top:
'Determines value of active cell and stores as S1 (string 1)then moves down one row
S1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
'Determines value of active cell and stores as S2 (string 2)
S2 = ActiveCell.Value
Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
If S1 = S2 Then
GoTo Top:
End If
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
GoTo Top:
Loop
Range("A1").Select
End Sub
Thanks in advance for your help......