Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Page Breaks Keep Rows Together


(!)

566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
12-May-2008, 06:02 PM #1
Excel Page Breaks Keep Rows Together
I am trying to set up a macro to put in automatic page breaks for a report sent to Managers for their Team Members. Some of these reports are 6 pages of data with several lines of data for each agent, including a total line, and seven different column headings. I would like to make sure data for an individual agent is not split onto two different pages, so the Agent Total AHT is with the data above it (column C). The total lines of data per page would be about 60. An example of the data is below:

Allred, Danielle 057203100 CSD Dom 11 CCD Domestic 52
Allred, Danielle 057203100 CSD Intl 12 CCD Intl 4
Allred, Danielle 057203100 CSD SMS 15 SMS Coordinator 24
Allred, Danielle 057203100 CSD SMS 16 CCD CRO 21
Allred, Danielle 057203100 CSD SMS 1261 Skymiles Service Ctr 34
Allred, Danielle 057203100 Gen Sales 1 Dom Service 11
Allred, Danielle Agent Total AHT 146
Alserda, Eva 043241700 CSD Dom 11 CCD Domestic 125
Alserda, Eva 043241700 CSD Dom 119 SLC CCD Coordinator 5
Alserda, Eva 043241700 CSD Intl 12 CCD Intl 12
Alserda, Eva 043241700 CSD SMS 15 SMS Coordinator 53
Alserda, Eva 043241700 CSD SMS 1261 Skymiles Service Ctr 65
Alserda, Eva 043241700 Gen Sales 1 Dom Service 11
Alserda, Eva Agent Total AHT 271

I have tried several things, including the Sub KeepTogether(), but it puts an inconsistent number of lines of data on each page and I couldn't get it to use a constant defined number.

Any assistance you could provide would be very much appreciated.

Thanks,
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-May-2008, 06:16 PM #2
This will put in page breaks where the data changes
Code:
Sub Page_Break_at_Change()
Do Until ActiveCell = ""
If ActiveCell <> ActiveCell.Offset(1, 0) Then
ActiveCell.Offset(1, 0).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
However, can't tell if you have all your data in one column. If you do, you need to extract the manager's names into a different column, and run the code with that column as the active cell (just click at the top).
Zack Barresse's Avatar
Computer Specs
Member with 5,419 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
12-May-2008, 07:06 PM #3
Have you thought of a pivot table? You could also do an autofilter and copy the data to their own sheets to prep for export/report, which would probably work better if you were going to email that data out. This can all be done programmatically. Just tell us exactly what you're looking for as far as your exporting/reporting is concerned. Sample data works very well too.
566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
13-May-2008, 02:32 PM #4
Thanks for your response. However, I tried this macro and it did not work correctly. The data is in separate columns. I think the change would be after the name in column A since I want to be sure this data is not separated onto a new page. There are a total of ten columns: (see listed below)
Name Emp# Work Area Skill Skill Name NCH Actual AHT AHT Goal Variance NCH %

I already have a macro set up that brings in the agents for a specific team, puts a gray fill in the Agent AHT Total line for each agent, bolds and adds borders. What I need now is to be sure the data for an agent is not separated on two different pages of the report. Each page will hold about 60 lines of data.

Is thi spossible?
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
13-May-2008, 03:01 PM #5
If you click into A2 (I assume that is where the first name is, with a header in A1) and then run the macro, it will read down the column and insert a page break after the end of one group of names and the beginning of another. It works fine for me.
I suspect you would want to run the page break macro first, then your other one.
566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
13-May-2008, 03:10 PM #6
Slurpee 55,

I did exactly what you said and used the macro. It places each name onto a separate page. I'm looking for a macro that will put about 60 lines of data on one page, but be sure one agent's data is not separated and is then on 2 different pages. There will be several agents' data together on one page, but the last agent on that page will have all of their data, including the line that has the Agent AHT Total.

I've tried to attached files to these postings, but it won't allow me to.

Thanks,
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
13-May-2008, 03:11 PM #7
However, as for getting all the data onto one page, well, you are going to have to format your sheet as portrait and use legal size paper.
-EDITED - changed landscape to portrait - EDITED-

Last edited by slurpee55; 13-May-2008 at 03:18 PM..
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
13-May-2008, 03:14 PM #8
If this is all in one cell
Allred, Danielle 057203100 CSD Dom 11 CCD Domestic 52
then it is different than
Allred, Danielle 057203100 CSD Intl 12 CCD Intl 4
so, yes, you will get a page break.
But if you have just
Allred, Danielle
and
Allred, Danielle (or just Allred, Allred)
in the cells, you will not get a page break until you get to
Alserda, Eva
Zack Barresse's Avatar
Computer Specs
Member with 5,419 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-May-2008, 03:17 PM #9
So, multiple names per page, just not over 60 records per page? Why not one name per page? Why not use your pivot table and use the names as a filter, loop through and print?
566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
13-May-2008, 03:19 PM #10
Why do I have to use legal paper landscape? I don't want all of the data on one page. It can be on 6 pages. The only thing I want to be sure of is that the last agent on the page has all lines of their data showing on that page. If it doesn't fit, then it needs to flow onto the next page.
566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
13-May-2008, 03:22 PM #11
The Team Leaders post the report for the agents to see. It would be a total waste of paper to have one agent on one piece of paper. With over 600 agents I'd be killing lots of trees.
Zack Barresse's Avatar
Computer Specs
Member with 5,419 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-May-2008, 03:22 PM #12
My question, 566290, is why mess with it at all? Why not just hit your Easy Button and put one name/agent per page??

Edit: You're not keeping electronically then? Maybe I'm not understanding either. Sounds like slurpee understands. I'll sit and watch.
566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
13-May-2008, 03:25 PM #13
Allred, Danielle is in column A. The name is not separated into two columns. The next column is the 9 digit employee number.
566290's Avatar
566290 566290 is offline
Computer Specs
Junior Member with 19 posts.
THREAD STARTER
 
Join Date: Jan 2008
Experience: Intermediate
13-May-2008, 03:29 PM #14
Keep the data electronically, but I also print it so the Team Leaders can post the data for the agents to see.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
13-May-2008, 04:21 PM #15
I had assumed from this line in your first post
"I would like to make sure data for an individual agent is not split onto two different pages"
that you wanted a separate page for each agent.
So what you want is to keep any group of seven lines together? (Will an agent always have 7 lines of data?)
If you have about 60 lines (what, really 7*9=63?) of data, you will need legal unless you want to make the rows quite small - standard paper only shows about 51 lines unless you make the rows about 10.5 high. Legal will show about 68 lines at a standard row height.
So Zack, what code do you recommend?
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Inserting specific number of rows in Excel and copying data in those inserted rows PincivMa Business Applications 12 18-Dec-2006 01:04 PM
Solved: Excel 97 page breaks slurpee55 Business Applications 7 25-Nov-2004 01:22 AM
Excel Printing problem - resets page breaks SET_Pat Business Applications 5 14-Sep-2004 01:00 PM
Page breaks in Access? Jen_Brown Business Applications 3 05-Feb-2003 09:49 AM
Excel 2000 Auto Page Breaks carolynw Business Applications 3 01-Nov-2002 09:46 AM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑