Is there a formula thet reads (If A2=A1,"",INSERT PAGE BREAK) ?

[Guest]

Excel spreadsheetwith many columns. Column A has customer names, other
columns have product, price, pak, etc. Sorted data by column A.
I want to print seperate pages for each customer. So I want to insert a page
break above each new customer. So if A1 and A2 differ, I wish to insert page
break above A2. Normally, I would go to cell A2 and alt-I,B. But I have
hundreds of lines with dozens of unique customers. So I'm looking to insert a
new column A (customers will then move to column B, of course), and formulate
in A2: (@if A2=A1,"",INSERT PAGE BREAK) . Then copy it to the entire column!
Does anyone how to do this? Thanks!

 

[Guest]

Enter and run this small macro:

Sub gsnu()
Dim r As Range, rr As Range

Cells.Select
ActiveSheet.ResetAllPageBreaks

For i = 2 To 65536
Set r = Cells(i, 1)
Set rr = Cells(i - 1, 1)
If Intersect(r, ActiveSheet.UsedRange) Is Nothing Then
Exit Sub
End If
If r.Value <> rr.Value Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=r
End If
Next
End Sub

The routine first clears all old pagebreaks. The routine scans down column
A (starting with A2). If it detects that the value in a cell is different
than the value above it, it will insert a page break above the new value.

 

[Bernard Liengme]

Here is a subrountine that will do it
Sub Macro1()
Set myrange = Selection
mytest = myrange(1)
For j = 2 To myrange.Count
If myrange(j) <> mytest Then
ActiveSheet.HPageBreaks.Add Before:=myrange(j)
End If
mytest = myrange(j)
Next

End Sub

See
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Bernard Liengme]

I meant to add:
Select column A before running subroutine
Happy Thanksgiving from Canada

 

[calrob]

I'm hoping someone can help me...I have a spread sheet of 100 names that span cells A1 to A100. I would like to insert a page break after every 20th name. So that when I print out the document only 20 names appear per page. Does anyone know how to do that?