How do I insert a page break every 5th row? marco?

%3CLINGO-SUB%20id%3D%22lingo-sub-2329176%22%20slang%3D%22en-US%22%3EHow%20do%20I%20insert%20a%20page%20break%20every%205th%20row%3F%20marco%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2329176%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20698%20line%20Excel%20file%20that%20I%20need%20separated%20at%20every%205th%20line.%26nbsp%3B%20How%20do%20I%20create%20a%20marco%20to%20accomplish%20this%20or%20is%20there%20a%20way%20to%20do%20this%20by%20using%20the%20insert%20page%20break%20option%3F%26nbsp%3B%20I'm%20using%20Excel%20for%20Office%20365%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2329176%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2330114%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20insert%20a%20page%20break%20every%205th%20row%3F%20marco%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2330114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1046328%22%20target%3D%22_blank%22%3E%40MLAcapgroupcom%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20InsertPageBreaks()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20ActiveSheet.ResetAllPageBreaks%0A%20%20%20%20m%20%3D%20Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20Range(%22A%22%20%26amp%3B%20m).Select%0A%20%20%20%20For%20r%20%3D%206%20To%20m%20Step%205%0A%20%20%20%20%20%20%20%20ActiveSheet.HPageBreaks.Add%20Before%3A%3DRange(%22A%22%20%26amp%3B%20r)%0A%20%20%20%20Next%20r%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a 698 line Excel file that I need separated at every 5th line.  How do I create a marco to accomplish this or is there a way to do this by using the insert page break option?  I'm using Excel for Office 365

1 Reply

@MLAcapgroupcom 

Here is a macro:

Sub InsertPageBreaks()
    Dim r As Long
    Dim m As Long
    ActiveSheet.ResetAllPageBreaks
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & m).Select
    For r = 6 To m Step 5
        ActiveSheet.HPageBreaks.Add Before:=Range("A" & r)
    Next r
End Sub