Repeat printing rows in multiple worksheets

[Guest]

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]

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

 

[Guest]

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]

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.

 

[Guest]

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]

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.

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

 

[Guest]

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 "view code", pasted what you wrote into the code window, and
saved.

See other comments below.

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.

I want the page break on row 49 in all 50 worksheets.

 

[Dave Peterson]

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.

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 "view code", pasted what you wrote into the code window, and
saved.

See other comments below.

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.

I want the page break on row 49 in all 50 worksheets.

 

[Guest]

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,
KevinG

 

[Dave Peterson]

You can do it all in one procedure:

Sub testmeBoth()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.PageSetup.PrintTitleRows = "$1:$11"
wks.PageSetup.PrintArea = "$A$1:$I$99"
wks.ResetAllPageBreaks
wks.HPageBreaks.Add before:=wks.Range("A56")
Next wks
End Sub

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,
KevinG

 

[Guest]

Thanks Dave. I've got it straightened out and working now.

Here's something else I need to do:

I need to view all of the values in column D rows 10 thorough 40 of each of
the 50 worksheets simultaneously. I tried a stab at a macro to copy col D for
each sheet to a new worksheet (Sheet55) in 30 contiguous columns, without
success.

Here's what I came up with:

Sub combineAll()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.Copy = "$D$10:$D40"
'Sheet55'.Paste = "A$10:A$40"
Next wks
End Sub

Did I get even remotely close?

Thanks,
KevinG

 

[Dave Peterson]

Do you mean 50 contiguous columns?

Option Explicit
Sub combineAll()
Dim DestCell As Range
Dim wks As Worksheet

Set DestCell = Worksheets("Sheet55").Range("a1")
For Each wks In ActiveWindow.SelectedSheets
'to know which sheet the data came from
DestCell.Value = "'" & wks.Name

'paste in one cell down
wks.Range("$D$10:$D40").Copy _
Destination:=DestCell.Offset(1, 0)

'get ready for the next time
Set DestCell = DestCell.Offset(0, 1)
Next wks
End Sub

Thanks Dave. I've got it straightened out and working now.

Here's something else I need to do:

I need to view all of the values in column D rows 10 thorough 40 of each of
the 50 worksheets simultaneously. I tried a stab at a macro to copy col D for
each sheet to a new worksheet (Sheet55) in 30 contiguous columns, without
success.

Here's what I came up with:

Sub combineAll()
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.Copy = "$D$10:$D40"
'Sheet55'.Paste = "A$10:A$40"
Next wks
End Sub

Did I get even remotely close?

Thanks,
KevinG

 

[Guest]

Thanks again Dave.

Do you know of another good source of info on macros for beginners? The link
you posted at the start of this thread was over my head.

KevinG

 

[Dave Peterson]

There's always books.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

And you can learn a lot by just lurking in the newsgroups, too.

Thanks again Dave.

Do you know of another good source of info on macros for beginners? The link
you posted at the start of this thread was over my head.

KevinG

 

[Soon-to-retire Teacher]

I have been unsucessful trying this... error message appears... "Reference is
not Valid". Here's what I do: open Excel a work sheet > File > Page Setup

I click 'Sheet' > in the Print Titles area, I place a 1 (for the row what I

want to repeat at the top of each page in the Rows to Repeat at Top Area >
click OK > error message... "Reference is not Valid."

I'm probably overlook something as plain as the nose on my face. Any help
appreciated.

 

[Gord Dibben]

Either click in the dialog box and point to row1 or enter 1:1 in the dialog
box.


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 11:51:03 -0800, Soon-to-retire Teacher <Soon-to-retire

 

[Soon-to-retire Teacher]

Thanks Gord, I'll try that tip.
BTW: How does a 'newbie' begin a NEW threat/questions? ...seems strange to
jump in on someone else's coat tails..

 

[Gord Dibben]

I don't use the CDO Interface as you do so can't speak to that.

Many other use the CDO so will jump in and give instructions.


Gord

 

[Soon-to-retire Teacher]

Gord: your tip worked nicely. However, nowhere in the Excel Help file do I
find your tip information... all it directs us to do is: after getting into
Page Setup: "in the Rows to repeat at top" box, enter the rows that contain
the coumn labels, then click Print. We are not instructed to enter $1:$1
rather than a 1. I had been trying to enter a 1 all along for Row 1 which
has my column heading info. Many Thanks.

 

[Pete_UK]

You might want to repeat, say, three rows at the top of each printed
sheet, so you would need to be able to indicate this - as $1:$3. Thus,
that is the syntax used, so if you only want one row then you have to
enter $1:$1.

Hope this helps.

Pete