Setting page breaks
Assuming country column is A
Sub Insert_PBreak()
Dim oldval As String
Dim rng As Range
Dim rng1 As Range
Set rng1 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
oldval = Range("A1")
For Each rng In rng1
If rng.Text < oldval Then
rng.PageBreak = xlPageBreakManual
oldval = rng.Text
End If
Next rng
End Sub
For the index sheet, record a macro while you run Advanced Filter on column
A to return Unique values only.
Then copy and paste to a new sheet.
Add your page numbers in an adjacent column.
Gord Dibben MS Excel MVP
On Wed, 13 Jan 2010 13:53:02 -0800, Basil
wrote:
Hiya,
I've been rattling through a custom report which is made through VBA. I have
done everything except for setting the page breaks.
The report outlines data and city information for multiple countries
vertically down the page (one country at a time). The countries vary in the
number of rows that they have - generally each will fit on 1 page.
Is there a way that I can set the page ranges to match the country sections?
I can make the top row always the same or something of needed.
Would really appreciate any help on the above.
The final step I want to do after this is to create on a separate sheet an
index of every country on this report and the corresponding page number. If
you have a suggestion on this, would also appreciate.
Thanks for any help,
Basil
|