I need to print 200+ reports in Excel 2003, all reports are formatted
identically, they just go to different recipients. I want a set print range
for all reports, so that every report is the same set # of rows. How can I
do this? Right now, I have to manually move the page break for each
report....
Thanks!
--
Amy

Amy,
I wrote the macro below for someone who had a similar issue.
By identifying that each page should start where the line has the
word 'Page' in it, I was able to find and create a page break
there.
I don't know if this will work for you but good luck.

'/=======================================/
' Sub Purpose: Put Page Breaks in current worksheet by looking
' for the item 'Page ' then inserting a page break on the
' line above the item 'Page '.
'
Sub PageBreak()

On Error GoTo exit_Sub

're-set any page breaks in the worksheet
Cells.Select
ActiveSheet.ResetAllPageBreaks

'go to 2nd line of report so that a page break won't be created
' before line 1 (which has the item 'Page ' on it)
Range("A2").Select

'find the item 'Page ' for the 1st time
Cells.Find(What:="Page ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

'continue finding the item 'Page ' and inserting page break until the item
' is not found. Then program ends.
Do
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Cells.FindNext(After:=ActiveCell).Activate
Loop

exit_Sub:
On Error Resume Next
Exit Sub

End Sub
'/=======================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select
''''''''''''''''YES'''''''''''''''' at the bottom of the post.



"Amy" wrote:

I need to print 200+ reports in Excel 2003, all reports are formatted
identically, they just go to different recipients. I want a set print range
for all reports, so that every report is the same set # of rows. How can I
do this? Right now, I have to manually move the page break for each
report....
Thanks!
--
Amy

I've tried it and it doesn't seem to work, but I'm new to Excel macros (my
experience is with Lotus more than Excel), so I'll keep trying!
Thank you for the macro!

--
Amy


"Gary Brown" wrote:

Amy,
I wrote the macro below for someone who had a similar issue.
By identifying that each page should start where the line has the
word 'Page' in it, I was able to find and create a page break
there.
I don't know if this will work for you but good luck.

'/=======================================/
' Sub Purpose: Put Page Breaks in current worksheet by looking
' for the item 'Page ' then inserting a page break on the
' line above the item 'Page '.
'
Sub PageBreak()

On Error GoTo exit_Sub

're-set any page breaks in the worksheet
Cells.Select
ActiveSheet.ResetAllPageBreaks

'go to 2nd line of report so that a page break won't be created
' before line 1 (which has the item 'Page ' on it)
Range("A2").Select

'find the item 'Page ' for the 1st time
Cells.Find(What:="Page ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

'continue finding the item 'Page ' and inserting page break until the item
' is not found. Then program ends.
Do
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Cells.FindNext(After:=ActiveCell).Activate
Loop

exit_Sub:
On Error Resume Next
Exit Sub

End Sub
'/=======================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select
''''''''''''''''YES'''''''''''''''' at the bottom of the post.



"Amy" wrote:

I need to print 200+ reports in Excel 2003, all reports are formatted
identically, they just go to different recipients. I want a set print range
for all reports, so that every report is the same set # of rows. How can I
do this? Right now, I have to manually move the page break for each
report....
Thanks!
--
Amy