Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell conten
I need to write a Macro or VB Script that will insert page breaks above each
row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell conten
Assuming your data has no blank cells in the column you are looking at, this
will get you started: Do While ActiveCell.Value < xlnull If ActiveCell.Value < ActiveCell.Offset(1, 0) Then ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell.Offset(1, 0) End If ActiveCell.Offset(1, 0).Select Loop End Sub This is just a quick piece of code, but will get you well on your way if you can do any VB prog at all. Good luck! "ronda" wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell conten
Several questions need to be answered first:
1. Is this a list of existing employees, who periodically change work locations (hence you are simply editing the location)? Or 2 Are you adding new employees at the bottom of the list? 3. Is the list sorted (or does it first need to be sorted) on the location column so that all workers working a specific location will be first grouped on a printed page? If not sorted, it seems you would end up with possibly a separate printed page for each employee? If this is not desired then a sort would be required after the data entry/edit but before the page break insert. "ronda" wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell conten
ronda
To insert the pagebreaks. 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 Note gocush's comments and questions also. Gord Dibben Excel MVP On Mon, 6 Dec 2004 21:45:01 -0800, gocush wrote: Several questions need to be answered first: 1. Is this a list of existing employees, who periodically change work locations (hence you are simply editing the location)? Or 2 Are you adding new employees at the bottom of the list? 3. Is the list sorted (or does it first need to be sorted) on the location column so that all workers working a specific location will be first grouped on a printed page? If not sorted, it seems you would end up with possibly a separate printed page for each employee? If this is not desired then a sort would be required after the data entry/edit but before the page break insert. "ronda" wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell co
OK,
1. It is a list of existing employees who periodically change work locations, however (this answers #2, also) I am downloading the list out of a network data program, so I am not recreating the list each time. 3. Yes, it is sorted. One last thing, I am super-newbie at VB script. Any suggestions for learning the language would be awesome. Thanks! "gocush" wrote: Several questions need to be answered first: 1. Is this a list of existing employees, who periodically change work locations (hence you are simply editing the location)? Or 2 Are you adding new employees at the bottom of the list? 3. Is the list sorted (or does it first need to be sorted) on the location column so that all workers working a specific location will be first grouped on a printed page? If not sorted, it seems you would end up with possibly a separate printed page for each employee? If this is not desired then a sort would be required after the data entry/edit but before the page break insert. "ronda" wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell co
Yah, let me reply again, because I'm stupid! In the place of the "i" should
I be giving the column letter, or a range of cells? I'm assuming! "Gord Dibben" wrote: ronda To insert the pagebreaks. 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 Note gocush's comments and questions also. Gord Dibben Excel MVP On Mon, 6 Dec 2004 21:45:01 -0800, gocush wrote: Several questions need to be answered first: 1. Is this a list of existing employees, who periodically change work locations (hence you are simply editing the location)? Or 2 Are you adding new employees at the bottom of the list? 3. Is the list sorted (or does it first need to be sorted) on the location column so that all workers working a specific location will be first grouped on a printed page? If not sorted, it seems you would end up with possibly a separate printed page for each employee? If this is not desired then a sort would be required after the data entry/edit but before the page break insert. "ronda" wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell co
ronda
Assuming the work location column has no blanks, just select top cell of that column then SHIFT + END + DownArrow then run the macro through ToolsMacroMacros. Gord On Tue, 7 Dec 2004 10:49:04 -0800, "ronda" wrote: Yah, let me reply again, because I'm stupid! In the place of the "i" should I be giving the column letter, or a range of cells? I'm assuming! "Gord Dibben" wrote: ronda To insert the pagebreaks. 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 Note gocush's comments and questions also. Gord Dibben Excel MVP On Mon, 6 Dec 2004 21:45:01 -0800, gocush wrote: Several questions need to be answered first: 1. Is this a list of existing employees, who periodically change work locations (hence you are simply editing the location)? Or 2 Are you adding new employees at the bottom of the list? 3. Is the list sorted (or does it first need to be sorted) on the location column so that all workers working a specific location will be first grouped on a printed page? If not sorted, it seems you would end up with possibly a separate printed page for each employee? If this is not desired then a sort would be required after the data entry/edit but before the page break insert. "ronda" wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I insert a page break before each change in the cell conten
Another option would be to use Data|Subtotals.
There's an option to insert a "page break between groups". And you'd get the subtotals, too! ronda wrote: I need to write a Macro or VB Script that will insert page breaks above each row that contains new contents in a particular cell. One column contains the work location for our employees in code form (i.e. N002), and page breaks need to be inserted to separate each work location, so there would need to be a page break at the point that the Work location code changes from N002 to N003. This is an extremely time-consuming task that has to be performed on multiple reports every two weeks. Your help is appreciated!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
Insert Page Break | Excel Discussion (Misc queries) | |||
Help! How do I insert a manual page break on a change in cell contents | Excel Discussion (Misc queries) | |||
change and/or remove page number watermark in page break preview | Excel Discussion (Misc queries) | |||
insert page break when there is a change in the row value | Excel Worksheet Functions |