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 "rows to repeat at top" 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 = "$1:$11"
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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

KevinG wrote:

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 "rows to repeat at top" for all 50 worksheets without having to
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 "repeat rows at the top" 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 "PrintArea =". 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

"Dave Peterson" wrote:

I'd use a macro.

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = "$1:$11"
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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

KevinG wrote:

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 "rows to repeat at top" for all 50 worksheets without having to
do so one worksheet at a time?

--

Dave Peterson

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:

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 "repeat rows at the top" 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 "PrintArea =". 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

"Dave Peterson" wrote:

I'd use a macro.

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = "$1:$11"
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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

KevinG wrote:

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 "rows to repeat at top" for all 50 worksheets without having to
do so one worksheet at a time?

--

Dave Peterson


--

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 = "$1:$11"
Next wks

Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintArea = "$A$1:$I$99"
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 "Private Sub
Worksheet_Activate()" or
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" - don't
recall - is yellow highlighted.

Thanks,
KevinG


"Dave Peterson" wrote:

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:

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 "repeat rows at the top" 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 "PrintArea =". 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

"Dave Peterson" wrote:

I'd use a macro.

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = "$1:$11"
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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

KevinG wrote:

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 "rows to repeat at top" for all 50 worksheets without having to
do so one worksheet at a time?

--

Dave Peterson


--

Dave Peterson

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 = "$1:$11"
Next wks
End Sub
Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintArea = "$A$1:$I$99"
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 = "$1:$11"
wks.PageSetup.PrintArea = "$A$1:$I$99"
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 "xxxx" in column A or something
that a program can determine, you may get a bit more help.

KevinG wrote:

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 = "$1:$11"
Next wks

Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintArea = "$A$1:$I$99"
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 "Private Sub
Worksheet_Activate()" or
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" - don't
recall - is yellow highlighted.

Thanks,
KevinG

"Dave Peterson" wrote:

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:

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 "repeat rows at the top" 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 "PrintArea =". 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

"Dave Peterson" wrote:

I'd use a macro.

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = "$1:$11"
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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

KevinG wrote:

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 "rows to repeat at top" for all 50 worksheets without having to
do so one worksheet at a time?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson