+ Reply to Thread
Results 1 to 8 of 8

Print different worksheets from multiple workbooks into 1 pdf

  1. #1
    Registered User
    Join Date
    07-09-2014
    Location
    Los Angeles, CA
    MS-Off Ver
    2013
    Posts
    3

    Print different worksheets from multiple workbooks into 1 pdf

    I am looking for a way, either programming with VBA or buying a piece of software to be able to print different worksheets from multiple workbooks into 1 pdf.

    I am trying to transform to a less paper environment. Each day I print out at least 15 - 20 different workbooks, then scan them together so that I can have 1 file for my records. Have found some tools for excel, works partially. Will print out the many sheets, but when I try to have it print out as a pdf it stops after each pdf and asks for a save as input. The one other clinker in the routine is my workbooks always contain at least 3 worksheets, the first 2 of which are not printed.

    What I do today...
    Open document folder for day of month
    Select workbook.
    Extract data I need.
    Select the worksheets I need printed, always 3 - XX. Can have upwards of 10 worksheets per.
    Do this for each workbook, up to 25 per day.
    Scan all printed pages to a pdf.
    Store pdf for future use.

    Besides wasting paper, it takes time.

    Would sure be nice to automate this.

    Thanks in advance for your help or input......

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Print different worksheets from multiple workbooks into 1 pdf

    The process I would recommend:

    1) Open each workbook and COPY the needed worksheet into a new workbook
    2) Once all the sheets have been copied into a single workbook, print that workbook as a whole to a single PDF
    3) Close the newly created workbook, save it if you wish.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Print different worksheets from multiple workbooks into 1 pdf

    In principle, it shouldn't be that hard to do in VBA.

    Loop through all the workbooks in the folder, opening (read only), processing and closing each one.
    Loop through each sheet in each workbook, copying 3-XX sheets to a new workbook.
    When last workbook has been processed, save the new workbook, if required and publish it as a .pdf file.
    Done. No paper. No scanning. No time.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    07-09-2014
    Location
    Los Angeles, CA
    MS-Off Ver
    2013
    Posts
    3

    Re: Print different worksheets from multiple workbooks into 1 pdf

    That may be what I have to do. Looking for something more automated. KU Tools for excel does something similar to what I need but only for printing to paper, not to a pdf. I have automated an extraction process for the data I need, now just trying to get it to print out my files into the 1 pdf.

    Going to give your idea a try and see how much time I save versus the old way.

    Thanks,

    Mike

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Print different worksheets from multiple workbooks into 1 pdf

    You're going to save tons of time.... it's only going to take 30 seconds per workbook to open and copy to the new temporary workbook, then you print that new workbook directly to PDF without printing on paper and then scanning at all.

    Having said that, this process could be automated with an Excel macro, too. Requirements would be:

    1) A specific folder that holds all the workbooks to process
    2) Knowing the name of the worksheet to copy out of all the workbooks in that folder...
    3) Each sheet would have to be uniquely named before copying into the new temp workbook, so some method for renaming those sheets
    4) Target destination and naming syntax for the PDF that will be created.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Print different worksheets from multiple workbooks into 1 pdf

    If you do it in VBA the end to end process would be automated. You'd need to define some rules about naming and location of the output file(s) but you could start with a dialogue box to navigate to the folder you want to process. That would be the only interaction.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    07-09-2014
    Location
    Los Angeles, CA
    MS-Off Ver
    2013
    Posts
    3

    Re: Print different worksheets from multiple workbooks into 1 pdf

    Thanks, Starting to work on that type solution now. Just wasn't too sure if there was a program around that would do it for me.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Print different worksheets from multiple workbooks into 1 pdf

    @JB: I don't think you'd need to worry about renaming the sheets. The copy process should take care of that. That said, probably easy enough to append part of the file name to differentiate them.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2013, 11:20 PM
  2. [SOLVED] Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets
    By DHartwig35805 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-27-2012, 11:38 AM
  3. Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets
    By Abhi_1977 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2012, 11:32 AM
  4. [SOLVED] Need2 batch print all the workbooks in 25 worksheets in one step
    By richie s. in forum Excel General
    Replies: 2
    Last Post: 06-26-2006, 10:46 AM
  5. Replies: 1
    Last Post: 04-06-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1