setting page breaks for multiple sheets in same workbook

[pmanning]

Hi, this is my fist post on this forum but I have used it quite a lot to learn about exel. My question is, is it possible to set the same page breaks in multiple sheets in the same workbook? There are over 80 sheets and the only way I have been able to do it is manually in each sheet as grouping the sheets doesn't allow page break setting or am I doing something wrong.

Thanks in advance.

Paul

 

[Andrew Poulsom]

Grouping the sheets and choosing Insert|Page Break from the menu worked for me.

 

[pmanning]

Thanks for the reply Andrew. I got it to work.

I have another question for you. On each of these sheets there are headings for the columns in the first row. With the page breaks in place now each sheet is 2 pages long which is fine. I want the headings from the first page repeated on the second page for each sheet. Again, I have tried grouping and using File/page setup but the option is greyed out when the sheets are grouped. Any suggestions! Hope this makes sense!

Thanks again.

Paul

 

[Andrew Poulsom]

That is definitely unavailable in Group mode and it's a pain.

One posibility is to record a macro while doing it on the first sheet. Then activate each sheet in turn and run the macro.

 

[pmanning]

I thought of that but given that there are so few steps to achieve the required result, I don't think it would save much time. Would it be possible to record the macro and amend it to tell it to run for all sheets in the work book since they are all formatted the same way with the same number of rows and columns. The only draw back is that all sheets have different names relating to customers. I'm only starting to read up on and learn VBA so after recording the macro for the first sheet I wouldn't know how to run it for all sheets. Any help would be greatly appreciated.

Paul

 

[shahram_98k]

Select on of your sheet and change it to page break view.
Then record a new Macro and go ahead and
-move the Breaks to where you need it
-Stop the Macro
-Go to Macro's, and select the Macro you Made and click on "Edit"
-you should see something like these lines:
"
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
"
Now all you need to wrap the code with a loop like this one:
"
Dim i As Long

For i = 1 To 80
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1

Sheets("Sheet" + LTrim(Str(i + 1)) + "").Select
Next i
"
-You can change 80 with any number or variable
-You can Change "Sheet" with your Sheet names (I assume your sheets are in numerical order) if not then you need to call the sheet to the right or left of active sheet.

I hope this helps. I know is late but it may help someone else.