Setting page breaks
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 |
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 |
All times are GMT +1. The time now is 10:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com