Fixing Page Breaks

[vic1]

I have a Workbook split into 26 alphabetical worksheets, each of which prints
out as several pages (300+ when the whole workbook is printed.)

I insert page breaks at the appropriate points and as long as I print there
and then, it is OK. However, if I save and close then reopen, the page breaks
have slipped and no longer 'break' in the right places - so they all have to
be set again, which is fairly time consuming on 300 pages!

Is there any means of fixing them so this does not happen?

 

[ryguy7272]

Whoa! 300 pages!! That's a lot of printing!! Save the trees!!

Not sure why you are losing those PageBreaks, but try this to get those page
breaks in faster:

Sub Insert_PBreak()
Dim OldVal As String
Dim rng As Range
OldVal = "YourCriteria"
For Each rng In Range("A1:A300") '<< change range
If rng.Text = OldVal Then
rng.Offset(1, 0).pagebreak = xlPageBreakManual
End If
Next rng
End Sub


Sub pagebreak()
Dim value1 As String
Dim value2 As String
Range("A2").Select
Do While ActiveCell.Value <> ""
value1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
value2 = ActiveCell.Value
If value1 <> value2 Then
ActiveCell.EntireRow.Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Else
End If
Loop
End Sub

Than, when you are done, view it on your monitor and resist the urge to
click that print button!!

Regards,
Ryan--

 

[vic1]

Thanks ................. but, sorry I'm no expert. What should I do with all
that code?

 

[Spiky]

I have a Workbook split into 26 alphabetical worksheets, each of which prints
out as several pages (300+ when the whole workbook is printed.)

I insert page breaks at the appropriate points and as long as I print there
and then, it is OK. However, if I save and close then reopen, the page breaks
have slipped and no longer 'break' in the right places - so they all have to
be set again, which is fairly time consuming on 300 pages!

Is there any means of fixing them so this does not happen?

The fault is in the Page Setup section called Scaling. (AFAIK) Excel
tends to lock this every now and then somehow, and when saving ignores
your alterations in Page Break Preview. This especially happens if the
file was originally created in a different version of Excel and now
you've upgraded since then, so it happens most often on files that are
used for years and years. The best way to fix this is to create a new
file in your current version of Excel.

Best/fastest way to do this:
Make a brand new, blank file, add all the worksheets you need and name
them. You need to copy your data, now. Many ways to do that, but here
is the fastest: In the old file, right-click on the Select All spot,
just to the left of Column A, choose Copy. Go to the new file to the
appropriate sheet and hit Enter, assuming that A1 is still selected.
Repeat 26 times. (do not simply copy/move the tab since this will copy
the Page Setup, also) This should preserve all data, all formulas, all
cell formatting, but NOT page setup or print areas.

The time consuming part will be redoing all your page layouts. But
hopefully for the last time.

Alternate methods:
(1) If it is only some of the worksheets have this problem, you can
probably do the same thing with just those sheets instead of all 26.
After the data is safely copied, delete the original sheet and move
the new one into the proper place.

(2) Go to Page Setup change Scaling to the other choice (it is
probably set to "Fit To" right now), then do your manual page breaks
again, and save/close/reopen again. See if that works. If not, try
switching back and changing the settings. Just try one worksheet at
first. But I think this less likely to work, and may actually take
longer than the new workbook method.