2

Update: added issue on Feedback Hub: https://aka.ms/AA65nqn

When I select sheets (Shift+Click to multiselect) in a macro-free workbook that each may or may not span multiple printed pages, select File > Print (using Microsoft Print to PDF, selecting "Print Active Sheets"), all pages don't get included in the PDF.

This happens when using File > Save As (PDF) as well.

Specifically, the last couple of sheets/pages are omitted from the result, which is a huge issue, since it basically renders the file useless for its purpose (= PDF report generation).

File has mixed orientation (Landscape/Portrait), many of the sheets span multiple printed pages, and the last two sheets are static one-pagers that are - even though I selected them - not included in the PDF.

I can reproduce the issue consistently with this example file. Example PDF output also available (note the two last sheets are missing). The last page looks like this:

Last page of example file which doesn't get included in PDF

Check the last page of the resulting PDF. It's not there!

Environment

  • Excel version 1906 (16.0.11727.20222 32-bit)
  • Windows 10 Pro 64-bit

Things I've checked for/tested:

  • All pages have same print quality (was noted as a potential issue elsewhere)
  • Using regular "Save As" and selecting PDF gives the same (bad) results.
  • CutePDF writer gives the same (bad) results.
  • Printing to a physical printer prints all pages (good!)
  • If I select only the last couple of sheets, they get included in the PDF as they should. As I expand my sheet selection and print to PDF, at some point it starts "losing" pages at the end of the document.

Observations/clues:

  • Total page count varies throughout the PDF document. See page 21 and 22 in the example PDF linked above, it says "Page 21 of 31", then "Page 22 of 30" (similar oddity can be observed with CutePDF)
  • Sheets with pivot tables that span multiple sheets seem to be the primary culprit: selecting these for printing seems to have higher likelihood of messing up the output.

I think this is mainly an issue with the print drivers (Microsoft Print to PDF or CutePDF) since printing to a professional printer works as intended, but I could be wrong.

What could be wrong, and how do you suggest I mitigate this? I need the PDF output; this is the primary purpose of the Excel file.

Improve this question
8
  • It's hard to help without the proper information. What is on the sheets/pages? What code have you used? Can you upload a copy of the sheet and/or pictures of what the last pages that does not print. Preferably the document but pictures may help too.
    – Andreas
    Sep 18, 2019 at 11:43
  • The book is macro-free, no code is involved. I just Shift+click to select all printable sheets and go to File > Print. Sheets are all just fairly simple pivot tables that span multiple pages each. Ie. one sheet can result in 2-3 pages in the PDF. Last couple of sheets are static/one-pagers. Updated question.
    – bernhof
    Sep 18, 2019 at 11:54
  • If we can't reproduce the problem then we can't help you either.
    – Andreas
    Sep 18, 2019 at 11:56
  • Point taken. It was surprisingly simple to reproduce the issue, see the example files linked in the post.
    – bernhof
    Sep 18, 2019 at 12:18
  • @Andreas I appreciate the feedback, and I understand the downvote is likely related to lack of information. If you feel that I could still provide more information to clarify, please do say so.
    – bernhof
    Sep 19, 2019 at 12:01

12 Answers 12

Reset to default
2
+50

I have used your example file to successfully duplicate the problem and I did find a solution.

The problem with this spreadsheet is that you have mixed orientations : Some sheets have the Landscape orientation while others are in Portrait. While printing, Excel forces you to specify one orientation and this does not work, for two reasons:

  1. Printing in Landscape Orientation will print all the selected sheets, but everything will be in Landscape Orientation, even the sheets marked as Portrait.

  2. Printing in Portrait Orientation will stop upon reaching sheet "Sheet2 (6)" which is too wide to print in Portrait orientation. Reducing the column-widths in "Sheet2 (6)" can let it print in Portrait Orientation, except that some columns will be truncated.

Excel simply does not let you print in mixed orientation, which I believe is an oversight of its developers, not adding an option for this case.

The way to generate a PDF file with variable Orientation is not to use a PDF printer. Use instead the ribbon File > Save As and save the selected sheets in PDF format. This will save the sheets, each with its own correct orientation.

Improve this answer
9
  • I think you've misread the print window. The settings displayed apply only to the page currently in the print preview. It's perfectly possible to print mixed orientation. I also don't believe it has anything to do with Print to PDF vs Save as PDF, but rather a more deep-founded issue with regard to PDF conversion in general. Two important observations here: (1) printing to an actual printer works perfectly fine, and (2) if you move the landscape sheet before Sheet2, you'll see it prints pages in both portait and landscape just fine, but will still lose pages in the end.
    – bernhof
    Sep 27, 2019 at 19:06
  • If I select all sheets to be printed in portrait, it still loses pages. (note that print layout is set to fit sheets to 1 page in width, so they shouldn't be too wide to print in portrait)
    – bernhof
    Sep 27, 2019 at 19:18
  • True, which is what I found. The Preview shows a perfect print file, but the PDF print itself is buggy and not perfect. I would guess that the Preview shows the normal printer layout and is not at all using the PDF printing software. You could enter a bug report for this via the Feedback Hub, but SaveAs is the workaround for the moment. I think there are more bugs with setting the Orientation in the Preview, as in my and your findings.
    – harrymc
    Sep 27, 2019 at 19:31
  • Well, at least it's good to know I'm not the only one seeing this issue - which means a bug report could yield results. Important to note that File > Save As does not correct the problem for me. Are you saying that if you download the example file anew and use File > Save As - you get the last two sheets ("Please print me") at the end in the PDF output??
    – bernhof
    Sep 28, 2019 at 18:04
  • Yes, I get all sheets when selecting them and doing SaveAs PDF. See my PDF file here. I'm using Office 2019, Excel version 16.0.11929.20298.
    – harrymc
    Sep 28, 2019 at 18:53
0

As a pragmatic work-around while you try and sort this out, you can use a PDF-editing file to add the missing pages into your main file from the print out of just those pages.

There are lots of free PDF editors of varying quality around. For what its worth, the one I use is called FlexiPDF and woul do the job.

Improve this answer
1
  • We're a consultancy delivering this file as a sort of productivity tool for a customer, so this type of workaround is definitely not a viable solution. Your answer may however be of help to others reading this question.
    – bernhof
    Sep 27, 2019 at 14:36
0

I have had the same frustration but in my case I have found a fix. When I was having the issue the Print Quality for all pages was blank. I went into each individual sheet and changed the print quality to 600dpi.
After this change all pages printed in one PDF setting in Microsoft Print to PDF. Hope this helps

Improve this answer
0

So I had the exact same issue. Of 9 pages, I got the first 7 to print. Then 8 & 9 were split into separate files...I actually had printed it successfully earlier this morning, multiple times.

  • I changed all the DPI settings individually.
  • I changed all the orientations to a single Landscape orientation, individually.
  • I forced both of those options to incorrect values, saved, then changed them back and saved.

I had no friggin' luck. I was constantly saving over the original file and keeping the same file name. Then I began bashing my head on my desk because I was unable to complete the exact same task I completed earlier.

Then, I asked my coworker if he had the PDF file open (don't know where this idea came from). He did. The original file I saved this morning is saved to a network location that he and I utilize. I made him close the "old" PDF file and I saved over the "old" PDF again. It worked!!!

I would suggest maybe file name change/location change. I found my fix by making him close the file, but in theory this could also be fixed by name change or location change.

Hope this helps. Good luck, I know this problem started with you a year ago. Ha!

Improve this answer
0

Appreciate that this is quite late but I was having the exact same issue and I do seem to have found a solution.

One of the comments above alluded to the cause of the problem being that the "Print Preview" was suggestion x number of pages, but when PDF'ing it was actually y number of pages and any excess pages were simply omitted from the final PDF.

The solution that worked for me was to go in to each sheet individually, go to 'Page Layout' and adjust the 'Height' of each sheet to be a fixed number of pages instead of 'Automatic'. Once I did this and saved as PDF then it worked.

enter image description here

Improve this answer
0

I had the same issue. Turns out I had a page break that I didn't/couldn't see on the 3rd-to-last tab, which caused the last two tabs to go to a separate PDF file. I cleared/reset the page breaks on all tabs to be sure, and the PDF issue was fixed - all tabs now included. My file had only one page per tab. I expect you could re-insert page breaks as needed, but I would clear all of them first and re-do them, making sure there are no "stray" page breaks.

Improve this answer
0

I was having the same issue @bernhof and it was driving me insane - the save as pdf, margin width, orientation suggestions were all no use. This fixed it for me - https://techcommunity.microsoft.com/t5/excel/excel-print-preview-not-matching-the-actual-printed-document/m-p/1030343 - I'm not sure if you can see the image I've tried to attach it here but there is an Excel Option under Advanced > General that you need to untick. I hope it helps - I know how frustrating this is!

image from above link

Improve this answer
1
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Jul 8 at 21:22
-1

Since file is MACRO free, therefore print to PDF is not possible, even using File, Print built in commands.

So that, I would like to suggest a method solves the issue.

How it works:

  • Form VIEW Tab click Page Break Preview.
  • Insure Page layout is correct, or if required alter it.
  • Save the File.
  • From File Menu click Save as.
  • Pick location, then from Save As Type drop down, select PDF.

You find this:

enter image description here

  • Select Standard Publication from Optimize For Radio Button.
  • You may also check the, Open File After Publishing Check box.
  • Now hit Option Button.

You find this:

enter image description here

  • Select an appropriate options as shown in Screen Shot above, or you may alter it as needed.

  • Now, finish with Ok.

  • Finally from previous Popup dialogue hit, Save button.

You find the PDF file open, then you may proceed in the way you want.

Improve this answer
8
  • 2
    Print to PDF is not dependent on macros!
    – harrymc
    Sep 27, 2019 at 12:30
  • This does not help me, unfortunately. If you believe it should, I suggest you try your method with the example file that I link to. See if the last sheet of the file is included in the resulting PDF. Whatever method I try (save as PDF, print to PDF, whatever options I select) I can't get the last page(s) out.
    – bernhof
    Sep 27, 2019 at 14:26
  • 1
    @bernhof,, check the Option Dialogue ,, Active Sheet is selected and while Print Preview you can find the pages to save to PDF ,, can be set Page Range in Option Dialogue,, and let me say what I've suggested in fully functional,, I've tested before I've posted here!!
    – Rajesh Sinha
    Sep 27, 2019 at 15:10
  • Yes, Print Preview displays things as they should look, but with the example file, the resulting PDF does not look like the print preview! I know your method "works" in the sense that it'll generate a PDF that - theoretically - should contain all sheets. But with the example file the PDF will not look as you'd expect.
    – bernhof
    Sep 27, 2019 at 18:45
  • @bernhof,, you need to check Excel data in Print Preview to insure Page layout & while SAVE As uisng the OPTION you may set choice to print ALL Pages or selected,, PDF is the out come of the exercise,, and what I've suggested is the only non VBA (macro) method to convert Excel Sheets to PDF.
    – Rajesh Sinha
    Sep 28, 2019 at 7:03
-1

Go to each sheet's "page setup" and ensure all settings are same - orientation, scaling, paper size, print quality.

Once I made all these same - I resolved my issue.

Improve this answer
1
  • You could be right (haven't tested it) but our file requires differing orientation, so it's really not a solution, but a less than ideal workaround. Thanks anyway, maybe this could help others.
    – bernhof
    Jun 19, 2020 at 8:31
-1

In my case, Excel increases the number of pages in some worksheets (as compared to preview mode) while printing to pdf. This mess things up as the total number of pages does not increase. The result is that sheets at the end of workbook do not fit in the total number of pages and are not printed.

For example, the workbook has 2 sheets. The first sheet has 5 pages in the active sheet print preview mode. The second sheet has 1 page in the print preview mode. The print preview mode of the entire workbook shows all 6 pages. After converting to pdf, the first sheet suddenly spans to 6 pages and the single page from sheet two is dropped.

To fix the problem, adjust the page layouts by changing page breaks to make sure that pages have some room to expand while printing. This prevents pages from spilling to next page and pushing out last page of last workbook out of the printing range.

Improve this answer
1
  • If this fixes your problem, an even better solution: Go to File -> Options -> Advanced. Under 'general' disable 'Scale content for A4 or 8.5x11" paper sizes'
    – PSZ_Code
    Dec 23, 2021 at 13:02
-1

I have found a problem of this issue. It is because Excel print to PDF all selected sheets to be printed are needed to have same Page setting e.g. Orientation (Portrait or Landscape, Paper Size, Page margin Normal .. Narrow etc.) and especially if any sheet insert page break, you have to ensure to Set Print Area on all pages in that sheet properly. I had this problem and tried to check all selected sheets to be printed to set them properly. I can successfully to print out to PDF.

Improve this answer
-1

I got the same issue on a file, the problem was that one of the first sheet was printed on 3 pages instead of 2 and so my last page of all sheets was not printed at all, he tought he was finished. I changed the place of the page break to follow what the pdf saver wanted, And so I got my last page.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .