Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
Can I automatically insert pagebreaks at certain points in a Excel Worksheet.
I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
I find the easiest thing to do is to use Data|Subtotals.
There's an option to put each group on its own page--and I get subtotals on top of it! cs_vision wrote: Can I automatically insert pagebreaks at certain points in a Excel Worksheet. I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
The problem is I have over 3900 records in my database and I need to insert
manual pagebreaks for each change unit numbers. There is a limitation with pagebreaks. "Dave Peterson" wrote: I find the easiest thing to do is to use Data|Subtotals. There's an option to put each group on its own page--and I get subtotals on top of it! cs_vision wrote: Can I automatically insert pagebreaks at certain points in a Excel Worksheet. I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
Sub InsertBreak_At_Change()
Dim i As Long For i = Selection.Rows.Count To 1 Step -1 If Selection(i).Row = 1 Then Exit Sub If Selection(i) < Selection(i - 1) And Not IsEmpty _ (Selection(i - 1)) Then With Selection(i) .PageBreak = xlPageBreakManual End With End If Next End Sub Select the 3900 + rows and run the above. Gord Dibben Excel MVP On Tue, 17 May 2005 15:36:04 -0700, cs_vision wrote: The problem is I have over 3900 records in my database and I need to insert manual pagebreaks for each change unit numbers. There is a limitation with pagebreaks. "Dave Peterson" wrote: I find the easiest thing to do is to use Data|Subtotals. There's an option to put each group on its own page--and I get subtotals on top of it! cs_vision wrote: Can I automatically insert pagebreaks at certain points in a Excel Worksheet. I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
You can have 1026 horizontal pagebreaks per sheet.
If you need more than that, then you can't use Data|Subtotals. But that limit occurs if you add them by hand or by using code like Gord posted. Maybe you could copy data to different worksheets, put in the pagebreaks there (how ever you decide), print those sheets and then delete those sheets. And if you really have mostly 3-4 rows per group, maybe a separate sheet really isn't the best use of paper. You could double up the size of the rows to make it look like each group is double spaced from the previous group. cs_vision wrote: The problem is I have over 3900 records in my database and I need to insert manual pagebreaks for each change unit numbers. There is a limitation with pagebreaks. "Dave Peterson" wrote: I find the easiest thing to do is to use Data|Subtotals. There's an option to put each group on its own page--and I get subtotals on top of it! cs_vision wrote: Can I automatically insert pagebreaks at certain points in a Excel Worksheet. I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
If I only need to sort by a certain column and only the first changing value
of that column, will this work? "Gord Dibben" wrote: Sub InsertBreak_At_Change() Dim i As Long For i = Selection.Rows.Count To 1 Step -1 If Selection(i).Row = 1 Then Exit Sub If Selection(i) < Selection(i - 1) And Not IsEmpty _ (Selection(i - 1)) Then With Selection(i) .PageBreak = xlPageBreakManual End With End If Next End Sub Select the 3900 + rows and run the above. Gord Dibben Excel MVP On Tue, 17 May 2005 15:36:04 -0700, cs_vision wrote: The problem is I have over 3900 records in my database and I need to insert manual pagebreaks for each change unit numbers. There is a limitation with pagebreaks. "Dave Peterson" wrote: I find the easiest thing to do is to use Data|Subtotals. There's an option to put each group on its own page--and I get subtotals on top of it! cs_vision wrote: Can I automatically insert pagebreaks at certain points in a Excel Worksheet. I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting pagebreaks into an Excel Worksheet
Select the cells in that certain column and then try it. If it doesn't work the
way you want, then close without saving. (I'm betting that if you don't exceed the number of horizontal pagebreaks per sheet, it'll work fine!) cs_vision wrote: If I only need to sort by a certain column and only the first changing value of that column, will this work? "Gord Dibben" wrote: Sub InsertBreak_At_Change() Dim i As Long For i = Selection.Rows.Count To 1 Step -1 If Selection(i).Row = 1 Then Exit Sub If Selection(i) < Selection(i - 1) And Not IsEmpty _ (Selection(i - 1)) Then With Selection(i) .PageBreak = xlPageBreakManual End With End If Next End Sub Select the 3900 + rows and run the above. Gord Dibben Excel MVP On Tue, 17 May 2005 15:36:04 -0700, cs_vision wrote: The problem is I have over 3900 records in my database and I need to insert manual pagebreaks for each change unit numbers. There is a limitation with pagebreaks. "Dave Peterson" wrote: I find the easiest thing to do is to use Data|Subtotals. There's an option to put each group on its own page--and I get subtotals on top of it! cs_vision wrote: Can I automatically insert pagebreaks at certain points in a Excel Worksheet. I have a worksheet that contains a column where there are unit numbers and I need to insert a pagebreak whereever the unit number changes. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can you change pagebreaks in excel 2007 before printing? | Excel Discussion (Misc queries) | |||
how do I remove multiple pagebreaks in an excel document in one g | Excel Discussion (Misc queries) | |||
Inserting worksheet in Excel | Excel Discussion (Misc queries) | |||
Excel Crashes after Inserting New Worksheet and Closing the File | Excel Discussion (Misc queries) | |||
Excel pagebreaks | Excel Programming |