Setting page breaks

[Basil]

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

 

[Gord Dibben]

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