Insert Page Break via loop

Andy D 2002

Board Regular
Joined
Apr 22, 2002
Messages
106
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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__
 
Upvote 0
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
 
Upvote 0
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__
 
Upvote 0
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
 
Upvote 0
Andy

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

Regards

Robb__
 
Upvote 0
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__
 
Upvote 0
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)
 
Upvote 0
Andy

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

with:

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

Regards

Robb__
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top