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
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)
'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
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 1 users browsing this thread. (0 members and 1 guests)