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.

KevinG wrote:

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" wrote:

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

KevinG wrote:

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" wrote:

You can do it all in one procedu

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 befo=wks.Range("A56")
Next wks
End Sub



KevinG wrote:

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" wrote:

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:

"Dave Peterson" wrote:

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.

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson