Auto select page break

scoudny

New Member
Joined
Aug 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello lords of the green X

I'm sorry if this is an obvious repeated question, I know I have seen something similar on here before but cannot find it now I need it.
I have a multi page excel workbook containing data and on one page the data is collated into a report, shimples.
Page 1 of the report populates cells E1:P45, page two, if required, E46:P90 and so on for 25 pages.
Although it pretty easy to go into page break preview and drag the blue line up and down to suit the amount of pages I report I am incredibly lazy and would rather the pc worked out how many pages to print for me.
I have a nice 'pages to print' counter, goading me that I will yet again have to drag the blue line up or down as microseconds of my life are wasted in this fashion.
I already have a macro button to print and save as pdf once I have set the page break, could this be something that could be added to the VBA code?

Apologies again if this is a bit samey and thank you in advance of any answers.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
438
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
VBA Code:
Sub PageBreakEachX()
Dim HPBreak As HPageBreak
Dim i As Long, lastrow As Long
Dim RowsPerPage As Integer
RowsPerPage = 45           'you want to print 45 rows per page
With ActiveSheet
    .ResetAllPageBreaks
    lastrow = .Cells(Rows.Count, "E").End(xlUp).Row
    For i = RowsPerPage + 1 To lastrow Step RowsPerPage
        On Error Resume Next
        For Each HPBreak In .HPageBreaks
            .Cells(i, 1).EntireRow.Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        Next HPBreak
    Next i
End With
Cells(1, 1).Select
End Sub
 
Solution

scoudny

New Member
Joined
Aug 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Thank you Kokosek, thats done the trick
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
438
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Great. Happy to help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,403
Messages
5,769,865
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top