Announcement

Collapse
No announcement yet.

Macro: Clear Page Breaks & Add Page Breaks

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    Re: Macros Page Break - Given Number Of Rows

    Try to remove all PageBreaks

    ActiveSheet.ResetAllPageBreaks

    jiuk

    Comment


    • #3
      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

      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

      Comment


      • #4
        Re: Macros Page Break - Given Number Of Rows

        doesn't this code add an additional row though?

        Comment


        • #5
          Re: Macros Page Break - Given Number Of Rows

          Thanks.

          Comment


          • #6
            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.

            Comment


            • #7
              Re: Macros Page Break - Given Number Of Rows

              Change this:

              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

              Comment


              • #8


                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

                Comment

                Working...
                X