I have an Excel workbook with multiple pairs of worksheets, where the first worksheet of each pair contains raw data and the second worksheet is a 'tidied-up' version of the raw data for printing off as a PDF for clients.

I am looking for a way to print off all the 'tidied-up' worksheets (about 40 of them) in a single operation. Those worksheets can all be identified by a common element in their name.

Each worksheet needs to be printed to file as a PDF with the name of the worksheet as the PDF file name. I have access to a PDF print-to-file utility called PDF995.

Thanks for ideas, Nigel.

  • If the worksheets stay the same each time, you could temporarily hide all but your tidy worksheets and then simply save as PDF, making sure to set the publish range to 'Entire Workbook' in the save options menu. (You can write a macro to automate the hiding/saving/unhiding.) – Andi Mohr Dec 18 '13 at 15:15
  • @AndiMohr Andi - If I manually select a range of worksheets and print to file as PDFs, all the worksheets are concatenated into one file (I need them as separate files). I'm wondering whether writing a macro to sequentially print each worksheet, so that each ends up as a separate PDF, would be a better idea. (The worksheet names don't change). But then I have the issue of ensuring that each PDF uses the name of the underlying worksheet as its file name. Any ideas on that one? Thanks, Nigel. – user277524 Dec 18 '13 at 15:54
  • Ah I see - sorry, misread where you said you wanted individual PDFs. I would do as you say - write a macro to automate the production of a PDF for each worksheet. To make sure the filename used matches the worksheet name you need to tweak the VBA code to get the result you need. Will dig out the code you need to change... – Andi Mohr Dec 18 '13 at 15:59
  • Take a look at this to get the filenames to match worksheets: stackoverflow.com/a/19940604/1607446 – Andi Mohr Dec 18 '13 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.