Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
I need to have a page break every 85th row. I need this done automatically,
because I have over 3800 rows. Any help is appreciated. JB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
I am sure this isn't the "right" way to do it, but I think it should
work. I just created a long spreadsheet, went into Page Break View, and moved the first dotted horizonal line to line 85. I then scrolled down, and now the break is on every 85th row (85, 170, 255, 340, ect ext) Anyway...hope this helps. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
Sub setPageBreaks()
Const lPageLength As Long = 85 Dim lRow As Long ActiveWindow.View = xlPageBreakPreview With ActiveSheet .ResetAllPageBreaks For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength .HPageBreaks.Add befo=Rows(lRow) Next lRow End With ActiveWindow.View = xlNormalView End Sub HTH -- AP "JAB" a écrit dans le message de news: ... I need to have a page break every 85th row. I need this done automatically, because I have over 3800 rows. Any help is appreciated. JB |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
When I go to run it, it says, "run-time error '424'", "object required" ,
then it points me to "For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength" I have no idea where to go from here. JB "Ardus Petus" wrote: Sub setPageBreaks() Const lPageLength As Long = 85 Dim lRow As Long ActiveWindow.View = xlPageBreakPreview With ActiveSheet .ResetAllPageBreaks For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength .HPageBreaks.Add befo=Rows(lRow) Next lRow End With ActiveWindow.View = xlNormalView End Sub HTH -- AP "JAB" a écrit dans le message de news: ... I need to have a page break every 85th row. I need this done automatically, because I have over 3800 rows. Any help is appreciated. JB |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
Ok, that latest macro worked for what it was written for. But, what I
thought was going to work, doesn't. Now, I need it to do a page break ever 3rd time the word "print" appears. Any chance I could get a macro for that? JB "Ardus Petus" wrote: Try: For lRow = 1 + lPageLength To .UsedRange.Rows.Count Step lPageLength (add a dot before UsedRange). If it does not work, please post back -- AP "JAB" a écrit dans le message de news: ... When I go to run it, it says, "run-time error '424'", "object required" , then it points me to "For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength" I have no idea where to go from here. JB "Ardus Petus" wrote: Sub setPageBreaks() Const lPageLength As Long = 85 Dim lRow As Long ActiveWindow.View = xlPageBreakPreview With ActiveSheet .ResetAllPageBreaks For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength .HPageBreaks.Add befo=Rows(lRow) Next lRow End With ActiveWindow.View = xlNormalView End Sub HTH -- AP "JAB" a écrit dans le message de news: ... I need to have a page break every 85th row. I need this done automatically, because I have over 3800 rows. Any help is appreciated. JB |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
Also, I need the page break to occur BEFORE the word "print", rather than
after. Thank you, JB "Ardus Petus" wrote: Try: For lRow = 1 + lPageLength To .UsedRange.Rows.Count Step lPageLength (add a dot before UsedRange). If it does not work, please post back -- AP "JAB" a écrit dans le message de news: ... When I go to run it, it says, "run-time error '424'", "object required" , then it points me to "For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength" I have no idea where to go from here. JB "Ardus Petus" wrote: Sub setPageBreaks() Const lPageLength As Long = 85 Dim lRow As Long ActiveWindow.View = xlPageBreakPreview With ActiveSheet .ResetAllPageBreaks For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength .HPageBreaks.Add befo=Rows(lRow) Next lRow End With ActiveWindow.View = xlNormalView End Sub HTH -- AP "JAB" a écrit dans le message de news: ... I need to have a page break every 85th row. I need this done automatically, because I have over 3800 rows. Any help is appreciated. JB |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
'-------------------------------------------------------------
Option Explicit Sub setPageBreaks() Const sSpecialWord = "print" Const iNbSpecial = 3 Const sSearchColumn = "A" Dim rCell As Range Dim iCount As Integer Dim iLastRow As Long Dim sFirstFound As String iLastRow = Cells(Rows.Count, sSearchColumn).End(xlUp).Row ActiveSheet.ResetAllPageBreaks Set rCell = Columns(sSearchColumn).Find( _ what:=sSpecialWord, _ after:=Cells(Rows.Count, sSearchColumn), _ LookIn:=xlValues, _ lookat:=xlPart, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) If rCell Is Nothing Then MsgBox sSpecialWord & " not found" Exit Sub End If sFirstFound = rCell.Address iCount = 1 Do Set rCell = Columns(sSearchColumn).FindNext(after:=rCell) iCount = iCount + 1 If iCount = 3 Then ActiveSheet.HPageBreaks.Add rCell iCount = 0 End If Loop Until rCell.Address = sFirstFound End Sub '------------------------------------------------------------ HTH -- AP "JAB" a écrit dans le message de news: ... Also, I need the page break to occur BEFORE the word "print", rather than after. Thank you, JB "Ardus Petus" wrote: Try: For lRow = 1 + lPageLength To .UsedRange.Rows.Count Step lPageLength (add a dot before UsedRange). If it does not work, please post back -- AP "JAB" a écrit dans le message de news: ... When I go to run it, it says, "run-time error '424'", "object required" , then it points me to "For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength" I have no idea where to go from here. JB "Ardus Petus" wrote: Sub setPageBreaks() Const lPageLength As Long = 85 Dim lRow As Long ActiveWindow.View = xlPageBreakPreview With ActiveSheet .ResetAllPageBreaks For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength .HPageBreaks.Add befo=Rows(lRow) Next lRow End With ActiveWindow.View = xlNormalView End Sub HTH -- AP "JAB" a écrit dans le message de news: ... I need to have a page break every 85th row. I need this done automatically, because I have over 3800 rows. Any help is appreciated. JB |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Page Break
Thats amazing...I wish I could program like that! Thank you very much!!!
JB "Ardus Petus" wrote: '------------------------------------------------------------- Option Explicit Sub setPageBreaks() Const sSpecialWord = "print" Const iNbSpecial = 3 Const sSearchColumn = "A" Dim rCell As Range Dim iCount As Integer Dim iLastRow As Long Dim sFirstFound As String iLastRow = Cells(Rows.Count, sSearchColumn).End(xlUp).Row ActiveSheet.ResetAllPageBreaks Set rCell = Columns(sSearchColumn).Find( _ what:=sSpecialWord, _ after:=Cells(Rows.Count, sSearchColumn), _ LookIn:=xlValues, _ lookat:=xlPart, _ searchorder:=xlByColumns, _ searchdirection:=xlNext, _ MatchCase:=False) If rCell Is Nothing Then MsgBox sSpecialWord & " not found" Exit Sub End If sFirstFound = rCell.Address iCount = 1 Do Set rCell = Columns(sSearchColumn).FindNext(after:=rCell) iCount = iCount + 1 If iCount = 3 Then ActiveSheet.HPageBreaks.Add rCell iCount = 0 End If Loop Until rCell.Address = sFirstFound End Sub '------------------------------------------------------------ HTH -- AP "JAB" a écrit dans le message de news: ... Also, I need the page break to occur BEFORE the word "print", rather than after. Thank you, JB "Ardus Petus" wrote: Try: For lRow = 1 + lPageLength To .UsedRange.Rows.Count Step lPageLength (add a dot before UsedRange). If it does not work, please post back -- AP "JAB" a écrit dans le message de news: ... When I go to run it, it says, "run-time error '424'", "object required" , then it points me to "For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength" I have no idea where to go from here. JB "Ardus Petus" wrote: Sub setPageBreaks() Const lPageLength As Long = 85 Dim lRow As Long ActiveWindow.View = xlPageBreakPreview With ActiveSheet .ResetAllPageBreaks For lRow = 1 + lPageLength To UsedRange.Rows.Count Step lPageLength .HPageBreaks.Add befo=Rows(lRow) Next lRow End With ActiveWindow.View = xlNormalView End Sub HTH -- AP "JAB" a écrit dans le message de news: ... I need to have a page break every 85th row. I need this done automatically, because I have over 3800 rows. Any help is appreciated. JB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Insert Page numbers without Footer | Excel Discussion (Misc queries) | |||
How do I remove the page number from a page break preview and kee. | Excel Discussion (Misc queries) | |||
Conditional page break if merged rows won't fit on the page. | Excel Worksheet Functions | |||
Removing a page break from excel | Excel Worksheet Functions | |||
Auto page numbering for several worksheets | Excel Worksheet Functions |