software

I need to print 53 Excel worksheets 50 of which will have multiple printed
pages and I need each printed page to display column headings by repeat
printing rows 1 through 11 for each of the 50 worksheet. How can I set the
print title quot;rows to repeat at topquot; for all 50 worksheets without having to
do so one worksheet at a time?

I'd use a macro.

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
Next wks
End Sub

First, select the 50 sheets that you want (click on the first tab and ctrl/shift
click on subsequent), then run that macro.

Then remember to ungroup those selected sheets.

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm

KevinG wrote:
gt;
gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; pages and I need each printed page to display column headings by repeat
gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; do so one worksheet at a time?

--

Dave Peterson

Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
though, but I did a copy paste from your email.

But now I have two new problems. My 50 worksheets come with the desired
text, column/row labels, formulas, etc. They also have a print area which
assumes no need for extra rows. That's the contingency I want to allow for by
setting the quot;repeat rows at the topquot; command for all 50, but I also need to
set print area and locate page breaks in the desired location.

For the print area issue I re-pasted just below what I'd pasted from your
email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
message but regardless it's working. The other matter is setting the page
break in the same location for all 50 sheets. Since I possess only a
dumbed-down concept of what I'm doing, I'm clueless on the page break macro.

Further assistance most appreciated.

KevinG

quot;Dave Petersonquot; wrote:

gt; I'd use a macro.
gt;
gt; Option Explicit
gt; Sub testme()
gt; Dim wks As Worksheet
gt; For Each wks In ActiveWindow.SelectedSheets
gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; Next wks
gt; End Sub
gt;
gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; click on subsequent), then run that macro.
gt;
gt; Then remember to ungroup those selected sheets.
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; KevinG wrote:
gt; gt;
gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; do so one worksheet at a time?
gt;
gt; --
gt;
gt; Dave Peterson
gt;

If you can post the routine you use to add the page breaks for one sheet, it
might be easily translated to do all the sheets.

And it's better to post the code with which you're having trouble--otherwise,
it's just too much of a guess.

KevinG wrote:
gt;
gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; though, but I did a copy paste from your email.
gt;
gt; But now I have two new problems. My 50 worksheets come with the desired
gt; text, column/row labels, formulas, etc. They also have a print area which
gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; set print area and locate page breaks in the desired location.
gt;
gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; message but regardless it's working. The other matter is setting the page
gt; break in the same location for all 50 sheets. Since I possess only a
gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt;
gt; Further assistance most appreciated.
gt;
gt; KevinG
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; I'd use a macro.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme()
gt; gt; Dim wks As Worksheet
gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; Next wks
gt; gt; End Sub
gt; gt;
gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; click on subsequent), then run that macro.
gt; gt;
gt; gt; Then remember to ungroup those selected sheets.
gt; gt;
gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt; KevinG wrote:
gt; gt; gt;
gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; do so one worksheet at a time?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

The only method I know for moving a page break is while viewing a worksheet
in Page Break Preview via click and drag or via Insert drop down menu
commands to Insert Page Break or Remove Page Break. Again the problem is that
method is via one worksheet at a time.

If I understand your other suggestion it's that I post here what I've
inserted into the Visual Basics Code window, which is:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
Next wks

Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
Next wks

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
Option Explicit

The sign of of trouble is a dialogue box containing the word error and when
I click OK the code window is displayed and either quot;Private Sub
Worksheet_Activate()quot; or
quot;Private Sub Worksheet_SelectionChange(ByVal Target As Range)quot; - don't
recall - is yellow highlighted.

Thanks,
KevinGquot;Dave Petersonquot; wrote:

gt; If you can post the routine you use to add the page breaks for one sheet, it
gt; might be easily translated to do all the sheets.
gt;
gt; And it's better to post the code with which you're having trouble--otherwise,
gt; it's just too much of a guess.
gt;
gt; KevinG wrote:
gt; gt;
gt; gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; gt; though, but I did a copy paste from your email.
gt; gt;
gt; gt; But now I have two new problems. My 50 worksheets come with the desired
gt; gt; text, column/row labels, formulas, etc. They also have a print area which
gt; gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; gt; set print area and locate page breaks in the desired location.
gt; gt;
gt; gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; gt; message but regardless it's working. The other matter is setting the page
gt; gt; break in the same location for all 50 sheets. Since I possess only a
gt; gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt; gt;
gt; gt; Further assistance most appreciated.
gt; gt;
gt; gt; KevinG
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; I'd use a macro.
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testme()
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; Next wks
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; gt; click on subsequent), then run that macro.
gt; gt; gt;
gt; gt; gt; Then remember to ungroup those selected sheets.
gt; gt; gt;
gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt; KevinG wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; gt; do so one worksheet at a time?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

The two routines testme and testme2 should go into a general module:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
Next wks
End Sub
Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
Next wks
End Sub

But they could be combined to do both at the same time:Option Explicit
Sub testmeBoth()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
Next wks
End Sub

But this still goes into a general module--not behind a worksheet and not under
ThisWorkbook.

And you're dragging pagebreaks to different locations--do you have a reason to
put the page breaks where you drag them--or is just based on look?

If you answer you want page breaks after every quot;xxxxquot; in column A or something
that a program can determine, you may get a bit more help.

KevinG wrote:
gt;
gt; The only method I know for moving a page break is while viewing a worksheet
gt; in Page Break Preview via click and drag or via Insert drop down menu
gt; commands to Insert Page Break or Remove Page Break. Again the problem is that
gt; method is via one worksheet at a time.
gt;
gt; If I understand your other suggestion it's that I post here what I've
gt; inserted into the Visual Basics Code window, which is:
gt;
gt; Option Explicit
gt; Sub testme()
gt; Dim wks As Worksheet
gt; For Each wks In ActiveWindow.SelectedSheets
gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; Next wks
gt;
gt; Sub testme2()
gt; Dim wks As Worksheet
gt; For Each wks In ActiveWindow.SelectedSheets
gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; Next wks
gt;
gt; Private Sub Worksheet_Activate()
gt;
gt; End Sub
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt;
gt; End Sub
gt; Option Explicit
gt;
gt; The sign of of trouble is a dialogue box containing the word error and when
gt; I click OK the code window is displayed and either quot;Private Sub
gt; Worksheet_Activate()quot; or
gt; quot;Private Sub Worksheet_SelectionChange(ByVal Target As Range)quot; - don't
gt; recall - is yellow highlighted.
gt;
gt; Thanks,
gt; KevinG
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; If you can post the routine you use to add the page breaks for one sheet, it
gt; gt; might be easily translated to do all the sheets.
gt; gt;
gt; gt; And it's better to post the code with which you're having trouble--otherwise,
gt; gt; it's just too much of a guess.
gt; gt;
gt; gt; KevinG wrote:
gt; gt; gt;
gt; gt; gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; gt; gt; though, but I did a copy paste from your email.
gt; gt; gt;
gt; gt; gt; But now I have two new problems. My 50 worksheets come with the desired
gt; gt; gt; text, column/row labels, formulas, etc. They also have a print area which
gt; gt; gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; gt; gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; gt; gt; set print area and locate page breaks in the desired location.
gt; gt; gt;
gt; gt; gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; gt; gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; gt; gt; message but regardless it's working. The other matter is setting the page
gt; gt; gt; break in the same location for all 50 sheets. Since I possess only a
gt; gt; gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt; gt; gt;
gt; gt; gt; Further assistance most appreciated.
gt; gt; gt;
gt; gt; gt; KevinG
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I'd use a macro.
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; Next wks
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; gt; gt; click on subsequent), then run that macro.
gt; gt; gt; gt;
gt; gt; gt; gt; Then remember to ungroup those selected sheets.
gt; gt; gt; gt;
gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; gt; gt; do so one worksheet at a time?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson



quot;Dave Petersonquot; wrote:

gt; The two routines testme and testme2 should go into a general module:I don't understand. I highlighted 50 worksheets, right clicked one of the
tabs, chose quot;view codequot;, pasted what you wrote into the code window, and
saved.

See other comments below.gt; Option Explicit
gt; Sub testme()
gt; Dim wks As Worksheet
gt; For Each wks In ActiveWindow.SelectedSheets
gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; Next wks
gt; End Sub
gt; Sub testme2()
gt; Dim wks As Worksheet
gt; For Each wks In ActiveWindow.SelectedSheets
gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; Next wks
gt; End Sub
gt;
gt; But they could be combined to do both at the same time:
gt;
gt;
gt; Option Explicit
gt; Sub testmeBoth()
gt; Dim wks As Worksheet
gt; For Each wks In ActiveWindow.SelectedSheets
gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; Next wks
gt; End Sub
gt;
gt; But this still goes into a general module--not behind a worksheet and not under
gt; ThisWorkbook.
gt;
gt; And you're dragging pagebreaks to different locations--do you have a reason to
gt; put the page breaks where you drag them--or is just based on look?
gt;
gt; If you answer you want page breaks after every quot;xxxxquot; in column A or something
gt; that a program can determine, you may get a bit more help.I want the page break on row 49 in all 50 worksheets.
gt; KevinG wrote:
gt; gt;
gt; gt; The only method I know for moving a page break is while viewing a worksheet
gt; gt; in Page Break Preview via click and drag or via Insert drop down menu
gt; gt; commands to Insert Page Break or Remove Page Break. Again the problem is that
gt; gt; method is via one worksheet at a time.
gt; gt;
gt; gt; If I understand your other suggestion it's that I post here what I've
gt; gt; inserted into the Visual Basics Code window, which is:
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme()
gt; gt; Dim wks As Worksheet
gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; Next wks
gt; gt;
gt; gt; Sub testme2()
gt; gt; Dim wks As Worksheet
gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; Next wks
gt; gt;
gt; gt; Private Sub Worksheet_Activate()
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt;
gt; gt; End Sub
gt; gt; Option Explicit
gt; gt;
gt; gt; The sign of of trouble is a dialogue box containing the word error and when
gt; gt; I click OK the code window is displayed and either quot;Private Sub
gt; gt; Worksheet_Activate()quot; or
gt; gt; quot;Private Sub Worksheet_SelectionChange(ByVal Target As Range)quot; - don't
gt; gt; recall - is yellow highlighted.
gt; gt;
gt; gt; Thanks,
gt; gt; KevinG
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; If you can post the routine you use to add the page breaks for one sheet, it
gt; gt; gt; might be easily translated to do all the sheets.
gt; gt; gt;
gt; gt; gt; And it's better to post the code with which you're having trouble--otherwise,
gt; gt; gt; it's just too much of a guess.
gt; gt; gt;
gt; gt; gt; KevinG wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; gt; gt; gt; though, but I did a copy paste from your email.
gt; gt; gt; gt;
gt; gt; gt; gt; But now I have two new problems. My 50 worksheets come with the desired
gt; gt; gt; gt; text, column/row labels, formulas, etc. They also have a print area which
gt; gt; gt; gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; gt; gt; gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; gt; gt; gt; set print area and locate page breaks in the desired location.
gt; gt; gt; gt;
gt; gt; gt; gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; gt; gt; gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; gt; gt; gt; message but regardless it's working. The other matter is setting the page
gt; gt; gt; gt; break in the same location for all 50 sheets. Since I possess only a
gt; gt; gt; gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt; gt; gt; gt;
gt; gt; gt; gt; Further assistance most appreciated.
gt; gt; gt; gt;
gt; gt; gt; gt; KevinG
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I'd use a macro.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; gt; Next wks
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; gt; gt; gt; click on subsequent), then run that macro.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Then remember to ungroup those selected sheets.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; gt; gt; gt; do so one worksheet at a time?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

These routines are not worksheet procedures that should go in one of those sheet
modules.

Delete it from the worksheet module where you added it.

Then in the VBE, Insert|Module.

Paste that Testmeboth routine into that module.

Then back to excel and use
tools|macro|macros
to run testmeboth macro.

KevinG wrote:
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; The two routines testme and testme2 should go into a general module:
gt;
gt; I don't understand. I highlighted 50 worksheets, right clicked one of the
gt; tabs, chose quot;view codequot;, pasted what you wrote into the code window, and
gt; saved.
gt;
gt; See other comments below.
gt;
gt;
gt; gt; Option Explicit
gt; gt; Sub testme()
gt; gt; Dim wks As Worksheet
gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; Next wks
gt; gt; End Sub
gt; gt; Sub testme2()
gt; gt; Dim wks As Worksheet
gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; Next wks
gt; gt; End Sub
gt; gt;
gt; gt; But they could be combined to do both at the same time:
gt; gt;
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testmeBoth()
gt; gt; Dim wks As Worksheet
gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; Next wks
gt; gt; End Sub
gt; gt;
gt; gt; But this still goes into a general module--not behind a worksheet and not under
gt; gt; ThisWorkbook.
gt; gt;
gt; gt; And you're dragging pagebreaks to different locations--do you have a reason to
gt; gt; put the page breaks where you drag them--or is just based on look?
gt; gt;
gt; gt; If you answer you want page breaks after every quot;xxxxquot; in column A or something
gt; gt; that a program can determine, you may get a bit more help.
gt;
gt; I want the page break on row 49 in all 50 worksheets.
gt;
gt; gt; KevinG wrote:
gt; gt; gt;
gt; gt; gt; The only method I know for moving a page break is while viewing a worksheet
gt; gt; gt; in Page Break Preview via click and drag or via Insert drop down menu
gt; gt; gt; commands to Insert Page Break or Remove Page Break. Again the problem is that
gt; gt; gt; method is via one worksheet at a time.
gt; gt; gt;
gt; gt; gt; If I understand your other suggestion it's that I post here what I've
gt; gt; gt; inserted into the Visual Basics Code window, which is:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testme()
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; Next wks
gt; gt; gt;
gt; gt; gt; Sub testme2()
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; Next wks
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Activate()
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt; Option Explicit
gt; gt; gt;
gt; gt; gt; The sign of of trouble is a dialogue box containing the word error and when
gt; gt; gt; I click OK the code window is displayed and either quot;Private Sub
gt; gt; gt; Worksheet_Activate()quot; or
gt; gt; gt; quot;Private Sub Worksheet_SelectionChange(ByVal Target As Range)quot; - don't
gt; gt; gt; recall - is yellow highlighted.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; KevinG
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If you can post the routine you use to add the page breaks for one sheet, it
gt; gt; gt; gt; might be easily translated to do all the sheets.
gt; gt; gt; gt;
gt; gt; gt; gt; And it's better to post the code with which you're having trouble--otherwise,
gt; gt; gt; gt; it's just too much of a guess.
gt; gt; gt; gt;
gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; gt; gt; gt; gt; though, but I did a copy paste from your email.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; But now I have two new problems. My 50 worksheets come with the desired
gt; gt; gt; gt; gt; text, column/row labels, formulas, etc. They also have a print area which
gt; gt; gt; gt; gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; gt; gt; gt; gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; gt; gt; gt; gt; set print area and locate page breaks in the desired location.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; gt; gt; gt; gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; gt; gt; gt; gt; message but regardless it's working. The other matter is setting the page
gt; gt; gt; gt; gt; break in the same location for all 50 sheets. Since I possess only a
gt; gt; gt; gt; gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Further assistance most appreciated.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; KevinG
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I'd use a macro.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; gt; gt; Next wks
gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; gt; gt; gt; gt; click on subsequent), then run that macro.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Then remember to ungroup those selected sheets.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; gt; gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; gt; gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; gt; gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; gt; gt; gt; gt; do so one worksheet at a time?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

OK I've deleted from the worksheet module and pasted testmeBoth to a general
module window. Seems to be working as desired.

Now what about the page break? I need to set each of the 50 worksheets to
break just after row 55 on all 50.

Thanks,
KevinGquot;Dave Petersonquot; wrote:

gt; These routines are not worksheet procedures that should go in one of those sheet
gt; modules.
gt;
gt; Delete it from the worksheet module where you added it.
gt;
gt; Then in the VBE, Insert|Module.
gt;
gt; Paste that Testmeboth routine into that module.
gt;
gt; Then back to excel and use
gt; tools|macro|macros
gt; to run testmeboth macro.
gt;
gt; KevinG wrote:
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; The two routines testme and testme2 should go into a general module:
gt; gt;
gt; gt; I don't understand. I highlighted 50 worksheets, right clicked one of the
gt; gt; tabs, chose quot;view codequot;, pasted what you wrote into the code window, and
gt; gt; saved.
gt; gt;
gt; gt; See other comments below.
gt; gt;
gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testme()
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; Next wks
gt; gt; gt; End Sub
gt; gt; gt; Sub testme2()
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; Next wks
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; But they could be combined to do both at the same time:
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Sub testmeBoth()
gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; Next wks
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; But this still goes into a general module--not behind a worksheet and not under
gt; gt; gt; ThisWorkbook.
gt; gt; gt;
gt; gt; gt; And you're dragging pagebreaks to different locations--do you have a reason to
gt; gt; gt; put the page breaks where you drag them--or is just based on look?
gt; gt; gt;
gt; gt; gt; If you answer you want page breaks after every quot;xxxxquot; in column A or something
gt; gt; gt; that a program can determine, you may get a bit more help.
gt; gt;
gt; gt; I want the page break on row 49 in all 50 worksheets.
gt; gt;
gt; gt; gt; KevinG wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; The only method I know for moving a page break is while viewing a worksheet
gt; gt; gt; gt; in Page Break Preview via click and drag or via Insert drop down menu
gt; gt; gt; gt; commands to Insert Page Break or Remove Page Break. Again the problem is that
gt; gt; gt; gt; method is via one worksheet at a time.
gt; gt; gt; gt;
gt; gt; gt; gt; If I understand your other suggestion it's that I post here what I've
gt; gt; gt; gt; inserted into the Visual Basics Code window, which is:
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; Next wks
gt; gt; gt; gt;
gt; gt; gt; gt; Sub testme2()
gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; gt; Next wks
gt; gt; gt; gt;
gt; gt; gt; gt; Private Sub Worksheet_Activate()
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt;
gt; gt; gt; gt; The sign of of trouble is a dialogue box containing the word error and when
gt; gt; gt; gt; I click OK the code window is displayed and either quot;Private Sub
gt; gt; gt; gt; Worksheet_Activate()quot; or
gt; gt; gt; gt; quot;Private Sub Worksheet_SelectionChange(ByVal Target As Range)quot; - don't
gt; gt; gt; gt; recall - is yellow highlighted.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; KevinG
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; If you can post the routine you use to add the page breaks for one sheet, it
gt; gt; gt; gt; gt; might be easily translated to do all the sheets.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; And it's better to post the code with which you're having trouble--otherwise,
gt; gt; gt; gt; gt; it's just too much of a guess.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; gt; gt; gt; gt; gt; though, but I did a copy paste from your email.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; But now I have two new problems. My 50 worksheets come with the desired
gt; gt; gt; gt; gt; gt; text, column/row labels, formulas, etc. They also have a print area which
gt; gt; gt; gt; gt; gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; gt; gt; gt; gt; gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; gt; gt; gt; gt; gt; set print area and locate page breaks in the desired location.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; gt; gt; gt; gt; gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; gt; gt; gt; gt; gt; message but regardless it's working. The other matter is setting the page
gt; gt; gt; gt; gt; gt; break in the same location for all 50 sheets. Since I possess only a
gt; gt; gt; gt; gt; gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Further assistance most appreciated.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; KevinG
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I'd use a macro.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; gt; gt; gt; Next wks
gt; gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; gt; gt; gt; gt; gt; click on subsequent), then run that macro.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Then remember to ungroup those selected sheets.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; gt; gt; gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; gt; gt; gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; gt; gt; gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; gt; gt; gt; gt; gt; do so one worksheet at a time?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

You can do it all in one procedu

Sub testmeBoth()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
wks.ResetAllPageBreaks
wks.HPageBreaks.Add befo=wks.Range(quot;A56quot;)
Next wks
End Sub
KevinG wrote:
gt;
gt; OK I've deleted from the worksheet module and pasted testmeBoth to a general
gt; module window. Seems to be working as desired.
gt;
gt; Now what about the page break? I need to set each of the 50 worksheets to
gt; break just after row 55 on all 50.
gt;
gt; Thanks,
gt; KevinG
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; These routines are not worksheet procedures that should go in one of those sheet
gt; gt; modules.
gt; gt;
gt; gt; Delete it from the worksheet module where you added it.
gt; gt;
gt; gt; Then in the VBE, Insert|Module.
gt; gt;
gt; gt; Paste that Testmeboth routine into that module.
gt; gt;
gt; gt; Then back to excel and use
gt; gt; tools|macro|macros
gt; gt; to run testmeboth macro.
gt; gt;
gt; gt; KevinG wrote:
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; The two routines testme and testme2 should go into a general module:
gt; gt; gt;
gt; gt; gt; I don't understand. I highlighted 50 worksheets, right clicked one of the
gt; gt; gt; tabs, chose quot;view codequot;, pasted what you wrote into the code window, and
gt; gt; gt; saved.
gt; gt; gt;
gt; gt; gt; See other comments below.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; Next wks
gt; gt; gt; gt; End Sub
gt; gt; gt; gt; Sub testme2()
gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; gt; Next wks
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; But they could be combined to do both at the same time:
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Sub testmeBoth()
gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; gt; Next wks
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; But this still goes into a general module--not behind a worksheet and not under
gt; gt; gt; gt; ThisWorkbook.
gt; gt; gt; gt;
gt; gt; gt; gt; And you're dragging pagebreaks to different locations--do you have a reason to
gt; gt; gt; gt; put the page breaks where you drag them--or is just based on look?
gt; gt; gt; gt;
gt; gt; gt; gt; If you answer you want page breaks after every quot;xxxxquot; in column A or something
gt; gt; gt; gt; that a program can determine, you may get a bit more help.
gt; gt; gt;
gt; gt; gt; I want the page break on row 49 in all 50 worksheets.
gt; gt; gt;
gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The only method I know for moving a page break is while viewing a worksheet
gt; gt; gt; gt; gt; in Page Break Preview via click and drag or via Insert drop down menu
gt; gt; gt; gt; gt; commands to Insert Page Break or Remove Page Break. Again the problem is that
gt; gt; gt; gt; gt; method is via one worksheet at a time.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If I understand your other suggestion it's that I post here what I've
gt; gt; gt; gt; gt; inserted into the Visual Basics Code window, which is:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; gt; Next wks
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sub testme2()
gt; gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; gt; wks.PageSetup.PrintArea = quot;$A$1:$I$99quot;
gt; gt; gt; gt; gt; Next wks
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Private Sub Worksheet_Activate()
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The sign of of trouble is a dialogue box containing the word error and when
gt; gt; gt; gt; gt; I click OK the code window is displayed and either quot;Private Sub
gt; gt; gt; gt; gt; Worksheet_Activate()quot; or
gt; gt; gt; gt; gt; quot;Private Sub Worksheet_SelectionChange(ByVal Target As Range)quot; - don't
gt; gt; gt; gt; gt; recall - is yellow highlighted.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; gt; KevinG
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If you can post the routine you use to add the page breaks for one sheet, it
gt; gt; gt; gt; gt; gt; might be easily translated to do all the sheets.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; And it's better to post the code with which you're having trouble--otherwise,
gt; gt; gt; gt; gt; gt; it's just too much of a guess.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Thanks Dave. This helped. David McRitchie's intro was complete Greek to me
gt; gt; gt; gt; gt; gt; gt; though, but I did a copy paste from your email.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; But now I have two new problems. My 50 worksheets come with the desired
gt; gt; gt; gt; gt; gt; gt; text, column/row labels, formulas, etc. They also have a print area which
gt; gt; gt; gt; gt; gt; gt; assumes no need for extra rows. That's the contingency I want to allow for by
gt; gt; gt; gt; gt; gt; gt; setting the quot;repeat rows at the topquot; command for all 50, but I also need to
gt; gt; gt; gt; gt; gt; gt; set print area and locate page breaks in the desired location.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; For the print area issue I re-pasted just below what I'd pasted from your
gt; gt; gt; gt; gt; gt; gt; email and edited in quot;PrintArea =quot;. I seem to be getting some sort of error
gt; gt; gt; gt; gt; gt; gt; message but regardless it's working. The other matter is setting the page
gt; gt; gt; gt; gt; gt; gt; break in the same location for all 50 sheets. Since I possess only a
gt; gt; gt; gt; gt; gt; gt; dumbed-down concept of what I'm doing, I'm clueless on the page break macro.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Further assistance most appreciated.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; KevinG
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I'd use a macro.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; gt; gt; gt; gt; Sub testme()
gt; gt; gt; gt; gt; gt; gt; gt; Dim wks As Worksheet
gt; gt; gt; gt; gt; gt; gt; gt; For Each wks In ActiveWindow.SelectedSheets
gt; gt; gt; gt; gt; gt; gt; gt; wks.PageSetup.PrintTitleRows = quot;$1:$11quot;
gt; gt; gt; gt; gt; gt; gt; gt; Next wks
gt; gt; gt; gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; First, select the 50 sheets that you want (click on the first tab and ctrl/shift
gt; gt; gt; gt; gt; gt; gt; gt; click on subsequent), then run that macro.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Then remember to ungroup those selected sheets.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; KevinG wrote:
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; I need to print 53 Excel worksheets 50 of which will have multiple printed
gt; gt; gt; gt; gt; gt; gt; gt; gt; pages and I need each printed page to display column headings by repeat
gt; gt; gt; gt; gt; gt; gt; gt; gt; printing rows 1 through 11 for each of the 50 worksheet. How can I set the
gt; gt; gt; gt; gt; gt; gt; gt; gt; print title quot;rows to repeat at topquot; for all 50 worksheets without having to
gt; gt; gt; gt; gt; gt; gt; gt; gt; do so one worksheet at a time?
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson