Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Insert Page Break via loop

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    in Column A i've company names e.g

    A4 = header
    A5:A8 = "Company A"
    A9:A10 = "Company B"
    A11:A22= "Company C"

    Is there a macro that i can use that inserts a page break where the name changes in column A?..
    thanks
    A
    Andy Devine

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Andy

    Try this:

    Sub insertBreaks()
    Dim c As Range
    With Worksheets("Sheet1")
    For Each c In .Columns(1).Cells
    If c.Address = "$A$1" Then GoTo Skip 'Skip Row 1
    If c <> c.Offset(-1, 0) Then 'If the cell above is not the same
    .Rows(c.Row).PageBreak = xlPageBreakManual 'Insert a manual pagebreak
    Else
    End If
    Skip:
    Next c
    End With
    End Sub

    I don't know how far down the cells are filled. You may like to amend the Column to something shorter.

    Any help?

    Regards

    Robb__

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi
    thanks very much for that...
    this creates a break before and after my heading which is in A4 - do you know what i need to do?

    Also a new question..where i'm going with this is to get users to print "print report" button & then macro creates 1 client report for each client (where column headings are in row 4)..question..if page 1 shows client 1 and page 2 shows client 2 etc, etc is there a easy way to show client name in say cell B1 (which would be Client 1 on Page 1, client 2 on page 2 etc..)
    Hope makes sense ..(?)
    Andy
    Andy Devine

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Andy

    In answer to the first part:

    Replace: If c.Address = "$A$1" Then GoTo Skip 'Skip Row 1


    with: If c.Row < 6 Then GoTo Skip 'Skip Row 1

    You may need to adjust the row number to suit your situation.

    I'm not sure what you are trying to do with the client names. Where do the names come from? When do you want them put into b2?

    Regards

    Robb__

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Robb
    thanks again for this...your revised insert break works really well

    Re: Headings - after running the macro page 1 shows information for client 1 only, page 2 shows info for client2 only etc..is there a way of getting the active client name into for example, cell B2, which will be a bold heading..?

    Andy

    Andy Devine

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Andy

    Where is the client name to be found ie where is it before it is to be put into b2?

    Regards

    Robb__

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi rob
    client names are in col A
    thanks
    andy

    Andy Devine

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Andy

    There may be an easier way, but this should work.

    For the first sheet, I have assumed the client name is in A5. If this is incorrect, you will need to adjust.

    After that, the code looks for each pagebreak. Since they are set at the change of the client names in Column1, I have assumed the first cell in Col1 after the pagebreak will contain the client name.

    It is then a matter of looping through the pagebreaks. As you will see, each page has to be printed separately.

    Sub PrintPages()
    Dim x As Integer 'Declare a variable to hold break number

    With Worksheets("Sheet1")
    .[B2] = .[A5] 'Copy the first client to B2
    .Worksheets("Sheet1").PrintOut from:=1, To:=1, copies:=1 'Print the first page

    x = .HPageBreaks.Count 'Count the number of pagebreaks to establish the number of pages to print

    For x = 1 To x 'For the number of pagebreaks
    .[B2] = .Range(.HPageBreaks(x).Location.Address) 'Client name will fill columnA, so copy to B2
    .Worksheets("Sheet1").PrintOut from:=x + 1, To:=x + 1, copies:=1 'Print the page - number will be PageBreak number plus 1
    Next x

    End With
    End Sub

    Unfortunately, I have not had a chance to test this.

    Any help?

    Regards

    Robb__

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    this is great Robb, thanks so much for your help
    sorry if client names are in column B not column A what do i need to do?
    (already changed.[b2] = .[b5] bit)

    Andy Devine

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Andy

    Just replace:
    .[B2] = .Range(.HPageBreaks(x).Location.Address)

    with:

    .[B2] = .Range(.HPageBreaks(x).Location.Address).Offset(0, 1)

    Regards

    Robb__

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •