Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Page Breaks | Excel Discussion (Misc queries) | |||
Setting and removing page breaks | Excel Programming | |||
Setting Vertical Page Breaks | Excel Programming | |||
setting page breaks. | Excel Programming | |||
Setting page breaks (another) | Excel Programming |