Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Print selective sheets and pages (2002)

    This is possibly a VBA question more than Excel, but since the application is in Excel, I'm starting here.
    Our office has a fax print driver (you can select File|Print and change the printer to a fax driver...then, the "print" starts a fax)
    Excel workbook(s) have multiple sheet tabs and within the various sheet tabs there are multiple pages.
    I would like to have a sheet in the beginning of the workbook into which a user could enter sheet names and pages within the respective sheets to print to the fax printer driver.

    Ideally, A1 might be a valid sheet name, then B1, C1, etc. valid pages within that sheet.
    A2 would be the next sheet and B2, etc. identifing pages to print.

    I want ONE fax to be sent with all of the selections. The selectons do not have the same format.
    So, essentially, a user could fax someone 2 pages from "sheet1" (although it has a more meaningful name), plus 4 pages from "sheet3" , etc.
    The pages being printed aren't necessarily contiguous.
    And the recipient would get ONE fax with these multiple pages as if I printed them and then manually faxed them.

    Creating a separate Excel workbook with the pages is fine...or almost anything would be fine if it can be accomplished.

    I don't know VBA, BTW. Can this be done? Phew!

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Print selective sheets and pages (2002)

    kweaver,

    Will almost but not quite work for you?

    My Excel add-in "Print Desired Pages" can do the following...
    Print specified pages on selected sheets, such as pages 2,4,6 on sheets 1,3, 5.
    Print all (set) print areas on all selected sheets on a single page (or more as necessary) stacked one directly above the other.
    Plus some more stuff.
    It might be worth playing around with.
    The Word.doc directions are attached.
    Available (free) upon direct request. Remove xxx from email address.

    Regards
    Jim Cone
    San Francisco, CA
    jim.conexxx@rcn.comxxx

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print selective sheets and pages (2002)

    Some immediate thoughts. I will have to think a little about it, as I understand your "desire", it is not a trivial solution and has several problems. I hope my comments will add some clarification/compromises and possibly input from others.

    1) If you wanted to print everything from various sheets, you would not need a macro: you could print multiple sheets as one by just manually selecting the mulitple sheets (using ctrl and/or shift) and file - print

    2) If you did not mind multiple print jobs, the macro could look thru your list and print each range from each sheet individually (you would have a "printjob" for each range) Note this is how I would print them if printing was desired of multiple ranges from multiple sheets. It is even how I would print multiple ranges in 1 sheet. I am not sure why this method would not work, even though you might send 5 print jobs they should all go one after the other.

    3) To get 1 print job, would most likely require looping thru each of the sheets listed and each of the ranges and then copying them (in some manner) to a new sheet. Once they would be "combined" the "New temp sheet" could be printed as 1 range.

    3A)The problem would be making this print range look correct. You woul have to add the ranges so that go onto "pages" correcty. The format would copy, though you would lose the format of the column widths so they might look "incorrect" if you want different column widths in different ranges. The combined pages would look like you copied and pasted them all into 1 range. If you could not do this manually to look correct (due to formatting constraints), you will have a problem with this method.

    3B) an alternative to copying/pasting would be to copy a "picture" of the range and then place those pictures into the correct sequence and print them. There are some limitations to the picture size so it might be an issue if you have large ranges to print (I think in xl97 it truncates after 61 rows or 16 columns)

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Print selective sheets and pages (2002)

    Do I assume correctly that your addin will essentially do the technique I alluded as #2: It will print each range as a separate output?

    Or do you use a different technique that you would like to share.

    Steve

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Print selective sheets and pages (2002)

    Hi Steve,

    It uses your #3B and does it for objects(shapes) or set print areas.
    It will also stack actual selections from multiple sheets, but as you said each sheet
    will have the same selected area. The only way I was able to work around that limitation
    was to use print areas.
    The major "secret" was one I borrowed from a Leo Heuser article in one of the no longer
    published E-letters. You put each picture INSIDE an individual row on the temp sheet. You can place
    about 38 rows inside one row before you have to shrink the picture. The program measures each
    picture before placing it in the row. A major advantage is page breaks are not a problem,
    though the spacing feature in the program has to take them into effect. It also looks pretty good.
    Not like you simply pasted a bunch of stuff on a sheet.

    Regards,
    Jim Cone
    San Francisco, CA

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print selective sheets and pages (2002)

    One additional method that might be easier to implement would be to print all of the ranges to files, then concatenate all of the files and send the file to the fax driver.
    Legare Coleman

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Print selective sheets and pages (2002)

    I've decided to "retire" the Print Desired Pages" add-in referenced in my previous post.
    A new Excel add-in I've written "Print Selections Only" is much easier to use and is
    dedicated to just printing selections from single or mulitple sheets in a workbook.
    The selections when printed are stacked, one above the other, on one or more pages.
    Setting print ranges is no longer necessary. Just make your selections and click the menu item.
    The one page Word.doc instructions/use file is attached.

    The add-in is available - free - upon direct request. Remove xxx from my email address.

    Jim Cone
    San Francisco, CA
    jim.coneXXX@rcn.comXXX

Posting Permissions

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