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!

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 Befo=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.

--
Gary's Student


"Gentleman" wrote:

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!

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 Befo=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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gentleman" wrote in message
...
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!

I meant to add:
Select column A before running subroutine
Happy Thanksgiving from Canada
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gentleman" wrote in message
...
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!