Try to remove all PageBreaks
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!
One of Dave Hawleys edit to 72 as required
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
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.
There are currently 2 users browsing this thread. (0 members and 2 guests)