Solved

How to add page break as query grows or shrinks (#2)

Posted on 2014-01-23
2
144 Views
Last Modified: 2014-02-10
I am using this macro:

Sub InsertHorizontalPageBreaks()
    Dim RowNbr As Long, ws As Worksheet
   
    Set ws = ActiveSheet
    ws.ResetAllPageBreaks
   
    For RowNbr = 1 To ws.UsedRange.Rows.Count
        If Left(ws.Cells(RowNbr, 1), 5) = "Race:" Then
            ws.Rows(RowNbr).PageBreak = xlPageBreakManual
        End If
    Next RowNbr
End Sub

It works 90% of the way. Say I have racers for race 1 on rows 2-5. On row 6, i have the next race # title. Then on row 7-10 in have racers for race 2. If I add racers to race 1, the page break stays above row 6, it does't move down to row 7. So that means all but one racer will be on one page, the last racer is going onto the top of the page with race 2, then below are the racers for race 2. How do i make it so the page brake follows right above the race #, as i add/delete racers?
0
Comment
Question by:brasiman
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

why not

Cleaning the other pagebreak at same time yout the new ones?

Sub InsertHorizontalPageBreaks()
    Dim RowNbr As Long, ws As Worksheet
    
    Set ws = ActiveSheet
    ws.ResetAllPageBreaks
    
    For RowNbr = 1 To ws.UsedRange.Rows.Count
        If Left(ws.Cells(RowNbr, 1), 5) = "Race:" Then
            ws.Rows(RowNbr).PageBreak = xlPageBreakManual
        Else
            ws.Rows(RowNbr).PageBreak = xlPageBreakNone
        End If
    Next RowNbr
End Sub

Open in new window

Regards
0
 

Author Comment

by:brasiman
Comment Utility
Two questions:
1. I ran this and it worked, but i discovered when i have things added/deleted from my data, making the number of rows on the query go up and down, the page break isn't following my data up and down. Every time i refresh the query, do i have to run the marco? If so thats a pain!
2. I saved my spreadsheet, then when i went back into the macro, i got an error:
"Run-time error '1004': Unable to set the PageBreak property of the Range class.

I copied your macro exactly as is.

Any ideas on these two items?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now