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.