PDA

View Full Version : Solved: Need help with this macro



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......

Djblois
06-06-2007, 07:51 PM
PAM,

Just to clarify what you are asking? You want to do a page break at every change in Teacher, Course, or Section? Also are all 3 in different columns or the same column? Can you post a small sample workbook?

Djblois
06-06-2007, 07:54 PM
Sorry my mistake

Djblois
06-06-2007, 07:59 PM
Here is what I would do, from what I get you are saying:

Dim i as long, finalrow as long
finalrow = Cells( Rows.count, 1).End(xlUp).row

For i = 1 to finalrow
if Range("A" & i) <> Range("A" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if
if Range("B" & i) <> Range("B" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if
if Range("C" & i) <> Range("C" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if

next

unmarkedhelicopter
06-07-2007, 02:17 AM
Here is what I would do, from what I get you are saying:

Dim i as long, finalrow as long
finalrow = Cells( Rows.count, 1).End(xlUp).row

For i = 1 to finalrow
if Range("A" & i) <> Range("A" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if
if Range("B" & i) <> Range("B" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if
if Range("C" & i) <> Range("C" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if

next
Sorry Djblois, but you say "for i = 1 to ..." then say "if ... range("A" & i - 1) ..." That's 0 in my book :think:

I'd say "for i = 2 to ..."
I also think that the active cell gets a lot of breaks :devil2:

Pam in TX
06-07-2007, 02:54 AM
Here is what I would do, from what I get you are saying:

Dim i as long, finalrow as long
finalrow = Cells( Rows.count, 1).End(xlUp).row

For i = 1 to finalrow
if Range("A" & i) <> Range("A" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if
if Range("B" & i) <> Range("B" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if
if Range("C" & i) <> Range("C" & i -1) then
ActiveWindow.SelectedSheets.HPageBreaks.add Before:=ActiveCell
end if

next

I keep getting an error message Method 'Range of object'_global failed on the
if Range("A" & i) <> Range("A" & i -1) then line

To further clarify they are seperate columns (always A,C,D) and always begin on row 7....

Thanks again for the help....

Bob Phillips
06-07-2007, 03:45 AM
This works for me



Dim i As Long

For i = 8 To Cells(Rows.Count, 1).End(xlUp).Row
If (Range("A" & i) <> Range("A" & i - 1) Or _
Range("B" & i) <> Range("B" & i - 1) Or _
Range("C" & i) <> Range("C" & i - 1)) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & i)
End If
Next

Djblois
06-07-2007, 04:25 AM
Sorry Djblois, but you say "for i = 1 to ..." then say "if ... range("A" & i - 1) ..." That's 0 in my book :think:

I'd say "for i = 2 to ..."
I also think that the active cell gets a lot of breaks :devil2:

Actually in most cases I would begin with i = 3 because 2 would automatically be a page break. I just wrote the code quickly before I went to bed. Xld, I was thinking about using ors instead of the way I did later when I went to bed.

Djblois
06-07-2007, 04:30 AM
This is the way it should work. Slightly modified Xld code:

Dim i As Long

For i = 9 To Cells(Rows.Count, 1).End(xlUp).Row
If (Range("A" & i) <> Range("A" & i - 1) Or _
Range("C" & i) <> Range("C" & i - 1) Or _ Range("D" & i) <> Range("D" & i - 1)) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A" & i)
End If
Next

You shouldn't start on 8 because if the column headings are in row 7, then row 8 is automatically different then 7. Also you got the error because I used Before:=ActiveCell, xld got it right with Range("A" & i) because with the loop you won't actually have an activecell, sorry I didn't relieze that last night I was tired.

Pam in TX
06-07-2007, 06:25 AM
That's it................... Works great.......

Thank you so very much........ I can't tell you how much time this will save my department.....

lucas
06-07-2007, 06:34 AM
Hi Pam,
Be sure to mark your thread solved when you get your answer using the thread tools at the top of the page....you can always post followup questions here even if it's marked solved.

Hi Daniel.....ha ha your now trying to help others....good to see.