VBA macro to paginate worksheet

[ihf]

I have a worksheet which has names in the first column (starting at A2). My macro sorts on that column. I now need to paginate the worksheet such that there is a page of names for each letter of the alphabet.
What I am doing now is manually creating the pagebreak with: Worksheets("Sheet1").Rows(20).PageBreak = xlPageBreakManual. Since the row number will change in the future (it is being pulled from an SQL Server), I need to be able to find the row number (in this case 20) each time the name changes its first letter. How to do this?

[Crooza]

Try this

Sub paginate()

ActiveSheet.ResetAllPageBreaks

Dim rownum As Integer
rownum = 3

Do While Range("A" & rownum).Value <> ""

If Left(Range("A" & rownum).Value, 1) <> Left(Range("A" & rownum - 1).Value, 1) Then
  Worksheets("Sheet1").Rows(rownum).PageBreak = xlPageBreakManual
End If

rownum = rownum + 1
Loop

End Sub

[ihf]

Thanks! That works except it takes over 1 minute to do the pagination. I think it is continuing beyond the last row with a name all the way to Excel's limits. An additional problem is that after the sorted names there are a few dozen rows with no name but with data in other columns and those rows should be on their own page. Think of them as "unnamed" orders. Part of the problem is that I couldn't get a simple copy from a temp column to another column to work with limits.
I wanted to do something like this:

Range("A2", Range("A2").End(xlDown)).Value = Range("H2", Range("H2").End(xlDown)).Value

but I get an error when I try it so I reverted to copying the whole column with

Range("A:A").Value = Range("H:H").Value

[Crooza]

It took less than a second to run on mine. Suggest you upload your sheet.