1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Page Breaks Keep Rows Together

Discussion in 'Business Applications' started by 566290, May 12, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    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,
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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).
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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. :)
     
  4. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    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?
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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.
     
  6. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    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,
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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-
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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?
     
  10. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    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.
     
  11. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    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.
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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. :)
     
  13. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    Allred, Danielle is in column A. The name is not separated into two columns. The next column is the 9 digit employee number.
     
  14. 566290

    566290 Thread Starter

    Joined:
    Jan 30, 2008
    Messages:
    19
    Keep the data electronically, but I also print it so the Team Leaders can post the data for the agents to see.
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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?
     
  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/711703