Excel - automatically adding page breaks within data array

[king1000]

I am dealing with a large amount of data in Excel (5000 rows). Each row represents a person for which certain data is held (telephone, town, age, etc) I have sorted the data in ascending order by town and would now like to insert a page break after each town (so when I print I have a seperate page for each town). I have so far done this manually but am sure there must be some way of setting things up so that the page breaks are inserted automatically after each town. Would appreciate your help and suggestions.

 

[GlennUK]

If you add a subtotal ( menu command Data/Subtotals ) for change of Town ( say, Count of Town ), and tick the Page Break Between Groups checkbox, that'll do something like what you've asked for.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[macropod]

Hi King,

Try:

Sub PrintFormat()
Dim SBar As Boolean
Dim RowCount As Long
Dim Percent As Integer
Dim i As Long, Col As Integer
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
With ActiveSheet
  Col = 1
  RowCount = .UsedRange.Rows.Count
  .ResetAllPageBreaks
  For i = 2 To RowCount
    If .Cells(i, Col).Value <> .Cells(i - 1, Col).Value Then _
      .Cells(i, Col).PageBreak = xlPageBreakManual
    If Int(i / RowCount * 100 + 0.5) = Percent + 1 Then
      Percent = Percent + 1
      Application.StatusBar = Percent & "% Done"
    End If
  Next
End With
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

where 'Col' is the number of the column in which the locality data are found.

The macro gives feedback via the statusbar as to its progress.


Cheers
[MS MVP - Word]

 

[king1000]

GlennUk and Macropod - many thanks for your answers. Macropod - I've tested your macro and I am very impressed! It works perfectly but I have just one remaining issue. The macro adds a page break whenever there is a change in value within the specified column but it also adds a page break if the value is the same but some of the letters are uppercase vs lowercsase. For example, if the value is "London" in one row and in the next row its "LONDON" it will treat these as 2 different values and add a page break. Is there any way to avoid this.

 

[macropod]

Hi King,

Strictly speaking, "London" is not the same as "LONDON". However, to treat them as being the same, change:
If .Cells(i, Col).Value <> .Cells(i - 1, Col).Value
to:
If UCase(.Cells(i, Col).Value) <> UCase(.Cells(i - 1, Col).Value)


Cheers
[MS MVP - Word]

 

[king1000]

Thanks again macropod - this is extemely helpful. Just one last question - the macro resets all the page breaks before inserting the horizontal breaks after each new locality. Is there anyway to maintain the vertical page breaks so they are not reset every time the macro is run? Many thanks

 

[Gavona]

Try something like:

ActiveSheet.HPageBreaks.delete

I think that the method may not work for the entire collection at a go so if that fails

For each pb in ActiveSheet.HPageBreaks
  pb.delete
Next pb

Gavin

 

[lionelhill]

Depending on your application, you might also like to consider pivot tables. If you declare your whole data as a pivot table, you can then produce a summary of entries per town. The benefit of this is that a single double-click on the line in the pivot table relating to a particular town will instantly generate a complete new worksheet listing all entries for that town. You can easily (1 or 2 clicks) reorganise your pivot table to give summaries based on other parts of the data.

This is particularly helpful when the data changes, and you suddenly need to generate a new, up-to-date printout for a particular town.