Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting Page Breaks In VBA (XP)

    I have a worksheet containing a large number of rows and I am trying dynamically to set page breaks. Column A contains teaching groups and I wish to apply a page break when the group name changes. I am using the following code which runs without errors but does not produce the desired effect.
    Any help would be appreciated.

    Sub PageBreaking()
    Sheets("Scores").Select
    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).PageBreak = xlPageBreakManual
    End If
    Next y
    End Sub
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Setting Page Breaks In VBA (XP)

    a simple non-macro solution could be done withthe SUBTOTALS (data - subtotals) and have it page break between groups

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Page Breaks In VBA (XP)

    Thanks for that, Steve but the worksheet will be used by a number of users with limited Excel experience. I want to set up the whole worksheet from a click of a button. This bit is the last requirement.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    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: Setting Page Breaks In VBA (XP)

    Rob

    You needed a slight tweak in your code:

    Sub PageBreaking()

    On Error Resume Next ' added this to get rid of what happens when you get to the last row
    Sheets("Scores").Select
    x = Sheets("Scores").Range("A4").CurrentRegion.Rows.Co unt

    'MsgBox x
    Dim ScGroup As Range
    Set ScGroup = Sheets("Scores").Range("A4:A" & x + 1)
    For y = 2 To x + 1
    'MsgBox y
    If ScGroup.Cells(y) <> ScGroup.Cells(y - 1) Then
    Worksheets("Scores").Rows(y + 3).PageBreak = xlPageBreakManual ' added 3 to the y value
    End If
    Next y
    End Sub



    Can I get back to my Gridlock puzzle now please <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Jerry

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Page Breaks In VBA (XP)

    Cheers Jezza. It now works. What confused me was thst the code ran but only one page break was set.
    Get gridding!
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    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: Setting Page Breaks In VBA (XP)

    Rob

    I have just been playing with your code and I think I may have found a little bug. You warned that the users will have little or no knowledge of Excel. So, from experience I know that this type of user does fiddle. If they inadvertently changed the Page Set up 1 pages wide to 1 pages tall this code would not work. I have therefore added to the code which will force the page set up on running the code, see what you think. I found that when I ran it in page break preview it worked a dream.

    Sub PageBreaking()

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

    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$G$100"
    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
    Jerry

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Page Breaks In VBA (XP)

    Hi Jerry,

    I tried the code but it doesn't work. The only page break that's set is a vertical one after column G.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    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: Setting Page Breaks In VBA (XP)

    Rob

    I have just recreated it here and it works fine. Lets get this right, at the moment my little test has a list of names in column A starting at A4 nothing else. I view the page in Page Break Preview and at that point only column A is highlighted. I F8 it to view it and cells A1 to G100 are highlighted and then it runs through your code inserting the other page breaks.
    Jerry

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Page Breaks In VBA (XP)

    I've just tried this on a different computer on the network and it worked. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    Thanks again.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  10. #10
    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: Setting Page Breaks In VBA (XP)

    Dare I say University/College/School networks, the bane of my life!!!
    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
  •