Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Page Break (Excel 2003)

    I have a customer list of about 400 name. I am able to sort the data and place the header on each new sheet, however, what I wish to do is to take the sorted data and have it print each town as a separate sheet of paper. In short I am asking for all the Town A's to be on one page Town B's to be on the next page and so on. Any suggestions?
    Ed

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Page Break (Excel 2003)

    Have you tried using the Built-in "subtotal" (data - subtotals) and select "Page break between groups"

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting Page Break (Excel 2003)

    You could use a macro to insert page breaks. In the following code, I assumed that you have one header row (lngStartRow = 2) and that the towns are in column A (strCol = "A"). You can modify this as needed.

    The code removes all existing horizontal page breaks, then insert new ones based on the values in the specified column. You should run the macro before printing the worksheet.

    Sub InsertPageBreaks()
    ' Change as needed
    Const lngStartRow = 2
    Const strCol = "A"

    Dim ws As Worksheet
    Dim pb As HPageBreak
    Dim i As Long
    Dim lngEndRow As Long

    Set ws = ActiveSheet

    ' Delete existing horizontal page breaks
    For i = ws.HPageBreaks.Count To 1 Step -1
    ws.HPageBreaks(i).Delete
    Next i

    ' Create new page breaks
    lngEndRow = Range(strCol & 65536).End(xlUp).Row
    For i = lngStartRow + 1 To lngEndRow
    If Not Range(strCol & i) = Range(strCol & (i - 1)) Then
    ws.HPageBreaks.Add Range(strCol & i)
    End If
    Next i

    Set ws = Nothing
    End Sub

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Page Break (Excel 2003)

    I tried it, however, my Towns are out in with Town, State zip format. When I do the subtotal, it places all the same zip codes on one page. One town may have as many as 15 zip codes. Is there a way of doing it without breaking up the data?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Page Break (Excel 2003)

    If the city name has a comma after it, then place this formula:

    <pre>=LEFT(A1,FIND(",",A1)-1)
    </pre>


    in the top cell of an empty column. Replace both A1 references with the address of the cell that contains the City, State Zip. Then copy it down as far as the data goes. You can then subtotal on this column. You can hide this column if you want.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Page Break (Excel 2003)

    Thanks all! Problem solved with the help of Woody's Lounge!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •