A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I insert multiple page breaks simultaneously in Excel?



 
 
Thread Tools Display Modes
  #1  
Old October 3rd 06, 03:29 PM posted to microsoft.public.excel.misc
marina
external usenet poster
 
Posts: 12
Default How do I insert multiple page breaks simultaneously in Excel?

I have a spreadsheet with multiple entries that have to be printed on
separate pages. I thought I could select the rows by clicking on the row and
Ctrl and then click on Insert Page Break - and boom, it will insert all 150
page breaks. Or do I really have to spend a whole hour doing this mindless
task manually? Please help.
Ads
  #2  
Old October 3rd 06, 03:43 PM posted to microsoft.public.excel.misc
Ron de Bruin
external usenet poster
 
Posts: 11,123
Default How do I insert multiple page breaks simultaneously in Excel?

Hi Marina

Maybe this macro will help ti insert a pagebreak every 20 rows (change to your number)

If row 1 is a header row and you want to print it on every page then
change RW + 1 to RW + 2 and use File>Page Setup>Sheet to fill in $1:$1
in the "Rows to repeat at top: " box.

This example will use row 1 till the last row with data in column A .

Sub Insert_PageBreaks()
Dim Lastrow As Long
Dim Row_Index As Long
Dim RW As Long

'How many rows do you want between each page break
RW = 20
With ActiveSheet
'Remove all PageBreaks
.ResetAllPageBreaks
'Search for the last row with data in Column A
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Row_Index = RW + 1 To Lastrow Step RW
.HPageBreaks.Add Befo=.Cells(Row_Index, 1)
Next
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Marina" > wrote in message ...
>I have a spreadsheet with multiple entries that have to be printed on
> separate pages. I thought I could select the rows by clicking on the row and
> Ctrl and then click on Insert Page Break - and boom, it will insert all 150
> page breaks. Or do I really have to spend a whole hour doing this mindless
> task manually? Please help.



  #3  
Old October 3rd 06, 06:01 PM posted to microsoft.public.excel.misc
marina
external usenet poster
 
Posts: 12
Default How do I insert multiple page breaks simultaneously in Excel?

Unfortunately, there is NO pattern as to how many rows come after each page
break. Is there still another way?

  #4  
Old October 3rd 06, 07:28 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default How do I insert multiple page breaks simultaneously in Excel?

If there's no pattern, how do you know where to put those pagebreaks when you do
it manually?

Maybe you have a keyword that indicates that a pagebreak goes above/below this
word???

If you have one, I bet Ron could modify that code to help you.

Marina wrote:
>
> Unfortunately, there is NO pattern as to how many rows come after each page
> break. Is there still another way?


--

Dave Peterson
  #5  
Old October 3rd 06, 07:32 PM posted to microsoft.public.excel.misc
Ron de Bruin
external usenet poster
 
Posts: 11,123
Default How do I insert multiple page breaks simultaneously in Excel?

>If you have one, I bet Ron could modify that code to help you.

Sure



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Dave Peterson" > wrote in message ...
> If there's no pattern, how do you know where to put those pagebreaks when you do
> it manually?
>
> Maybe you have a keyword that indicates that a pagebreak goes above/below this
> word???
>
> If you have one, I bet Ron could modify that code to help you.
>
> Marina wrote:
>>
>> Unfortunately, there is NO pattern as to how many rows come after each page
>> break. Is there still another way?

>
> --
>
> Dave Peterson



  #6  
Old October 3rd 06, 09:41 PM posted to microsoft.public.excel.misc
marina
external usenet poster
 
Posts: 12
Default How do I insert multiple page breaks simultaneously in Excel?

I just now figured out a different way to get to my goal. But I'd still like
to know how to insert multiple page breaks simultaneously. Yes, there is no
pattern because the spreadsheet has multiple people's names and each person
has a different number of rows of information, so I wanted each person's
information to go onto the next page. So, I was wondering if I could just
click on Ctrl and select the rows where the new person begins and then insert
page breaks for all of them simultaneously.
  #7  
Old October 4th 06, 12:36 AM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default How do I insert multiple page breaks simultaneously in Excel?

This macro will place a pagebreak at every change in name in column A.

Maybe that will work?

Sub InsertBreak_At_Change()
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 3 Oct 2006 13:41:02 -0700, Marina >
wrote:

>I just now figured out a different way to get to my goal. But I'd still like
>to know how to insert multiple page breaks simultaneously. Yes, there is no
>pattern because the spreadsheet has multiple people's names and each person
>has a different number of rows of information, so I wanted each person's
>information to go onto the next page. So, I was wondering if I could just
>click on Ctrl and select the rows where the new person begins and then insert
>page breaks for all of them simultaneously.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Control Excel page breaks during sort? Lori H Excel Discussion (Misc queries) 1 August 4th 06 03:29 AM
How to show page breaks by default in Excel 2003 Beau Setting up and Configuration of Excel 0 March 3rd 06 02:22 PM
how to insert page numbering in an excel cell Kevin Excel Discussion (Misc queries) 0 November 16th 05 09:45 PM
Auto insert page breaks in Excel, based on a sort of numbers peterc89 Excel Discussion (Misc queries) 0 November 9th 05 03:41 PM
How do insert page numbers in excel Timothy Excel Discussion (Misc queries) 1 March 12th 05 08:32 PM


All times are GMT +1. The time now is 09:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.