Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page Breaking Macro (2000)

    I found this macro in this forum and I would like to make adjustments to it.
    Right now the breaking column is in A and starts in row 4. I would like to know where to make changes if my column is for example B and the row is 2. I tried changing some of the code to my specs but I don't understand the x and y coding. The macro works if I adjust my sheet to the macro but if I change it to a different column and row then it doesn't work. Where do I make adjustments:

    Sub PageBreaking()

    On Error Resume Next
    Sheets("Scores").Select

    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$48174"
    With ActiveSheet.PageSetup

    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    End With

    x = Sheets("Scores").Range("A4").CurrentRegion.Rows.Co unt


    Dim ScGroup As Range
    Set ScGroup = Sheets("Scores").Range("A4:A" & x + 1)
    For y = 2 To x + 1

    If ScGroup.Cells(y) <> ScGroup.Cells(y - 1) Then
    Worksheets("Scores").Rows(y + 3).PageBreak = xlPageBreakManual
    End If
    Next y
    End Sub

    Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page Breaking Macro (2000)

    In the line

    x = Sheets("Scores").Range("A4").CurrentRegion.Rows.Co unt

    you must change A4 to B2, and you must change the line

    Set ScGroup = Sheets("Scores").Range("A4:A" & x + 1)

    to ...Range("B2:B" & x + 1)

    Whether more adjustments are needed depends on the structure of your worksheet.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaking Macro (2000)

    Those are the changes I made to the macro but it didn't page break correctly. I think the line - Worksheets("Scores").Rows(y + 3).PageBreak = xlPageBreakManual is where I should adjust. What does the code Rows(y + 3) do?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page Breaking Macro (2000)

    If the value of a cell in the column you're looking at is NOT equal to the value of the cell above it, a page break is placed 3 rows below that cell.

    Since you haven't told us in which thread you found the code, I do not know where this specific requirement came from, and I don't know where you would want the page breaks.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaking Macro (2000)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    The thread starts at <post#=476588>post 476588</post#> - Setting Page Breaks in VBA.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page Breaking Macro (2000)

    Try changing

    Rows(y + 3).PageBreak = xlPageBreakManual

    to

    Rows(y + 1).PageBreak = xlPageBreakManual

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaking Macro (2000)

    That worked. Can you tell me the difference between Y+1 and Y+3........If my range was B3, would I have to change it to Y+?. Thanks for your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page Breaking Macro (2000)

    If you start at row 2 you use y + 1
    If you start at row 3, you use y + 2
    If you start at row 4, you use y + 3
    If you start at row 7, you use y + 6
    If you start at row 12, you use y + 11
    etc.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Breaking Macro (2000)

    Thanks Hans. Does Y represent the header row?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Page Breaking Macro (2000)

    No, y is a loop counter. It starts at the row number of the first data row, and increases to the number of the last data row + 1 (the + 1 is used to insert a page break below the last row with data). So in your situation, you start with y = 2, then y = 3, y = 4, etc. Each time, the code checks if the cell in row y is equal to the cell in row y-1 (the row above)

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Page Breaking Macro (2000)

    Linda

    The code initially counts the number of rows that the list contains this is denoted as X

    Y is introduced as a loop from 2 to x+1 (it satrts at two as there was a header in row 1)

    Bandido originally had a gap of 3 or 4 rows in his sheet so I had to adjust his code by adding 3 to X lower down in the code so that it could find the start of each list to allow the page break

    Hope that helps
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •