Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    VB:
    ActiveSheet.ResetAllPageBreaks 
    
    
    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
    VB:
    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 
    
    

    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:
    VB:
    PrArea = pageBegin & ":" & "$H$" & Trim$(Str$(q)) 
    
    
    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