Insert Page Break via loop

[Andy D 2002]

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

[Robb__]

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__

[Andy D 2002]

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

[Robb__]

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__

[Andy D 2002]

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

[Robb__]

Andy

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

Regards

Robb__

[Andy D 2002]

hi rob
client names are in col A
thanks
andy

[Robb__]

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__

[Andy D 2002]

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)

[Robb__]

Andy

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

with:

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

Regards

Robb__