Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Macro: Clear Page Breaks & Add Page Breaks

  1. #1
    Join Date
    28th November 2006
    Posts
    32

    Macro: Clear Page Breaks & Add Page Breaks

    My first post, and my first attempt at Macros.

    I need to remove all existing page breaks in a document and add a page break every 72 rows. I've tried some similar codes from this forum with other functions that I don't need in it...could someone help me with this? Any help is appreciated. Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Macros Page Break - Given Number Of Rows

    Try to remove all PageBreaks
    [vba]
    ActiveSheet.ResetAllPageBreaks
    [/vba]
    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Macros Page Break - Given Number Of Rows

    see http://www.ozgrid.com/Excel/excel-page-breaks.htm
    One of Dave Hawleys edit to 72 as required

    jiuk
    [vba]
    Sub PrintAreaWithpageBreaks()
    Dim pages As Integer
    Dim pageBegin As String
    Dim PrArea As String
    Dim i As Integer
    Dim q As Integer
    Dim nRows As Integer, nPagebreaks As Integer
    Dim R As Range
    Set R = ActiveSheet.UsedRange
    'add pagebreak every 40 rows
    nRows = R.Rows.Count
    If nRows > 40 Then
    nPagebreaks = Int(nRows / 40)
    For i = 1 To nPagebreaks
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=R.Cells(40 * i + 1, 1)
    Next i
    End If
    'can be used in a separate macro, as I start counting the number of pagebreaks
    pages = ActiveSheet.HPageBreaks.Count
    pageBegin = "$A$1"
    For i = 1 To pages
    If i > 1 Then pageBegin = ActiveSheet.HPageBreaks(i - 1).Location.Address
    q = ActiveSheet.HPageBreaks(i).Location.Row - 1
    PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q))
    ActiveSheet.PageSetup.PrintArea = PrArea
    ' the cell in column 1 and in the row immediately below the pagebreak
    ' contains text for the footer
    ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)
    ' ActiveSheet.PrintOut copies:=1
    Next i
    End Sub
    [/vba]

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th November 2006
    Posts
    32

    Re: Macros Page Break - Given Number Of Rows

    doesn't this code add an additional row though?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th November 2006
    Posts
    32

    Re: Macros Page Break - Given Number Of Rows

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th November 2006
    Posts
    32

    Re: Macros Page Break - Given Number Of Rows

    something seems to be wrong...not sure what it is but I copied the code exactly. The only thing I changed was 40 to 72, and the page breaks appear to have been entered correctly, but only about 2 pages seem to be printing out, when there should be about 250. I can't seem to adjust which columns I want either.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Macros Page Break - Given Number Of Rows

    Change this:
    [vba]
    PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q))
    [/vba]
    To the areas You need look at Current Region to cover the area or data umless You want to restrict this

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Macros Page Break - Given Number Of Rows

    I can't seem to adjust which columns I want either.
    What are You trying to do?

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Demo: Adjust page breaks to control what is printed on each page
    By Dave Hawley in forum Free Microsoft Excel 2007 Tutorials
    Replies: 0
    Last Post: July 26th, 2008, 01:47

Bookmarks

Posting Permissions

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