+ Reply to Thread
Results 1 to 10 of 10

VBA for print if cell contains, then choose printer. Have code just need advice.

  1. #1
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    VBA for print if cell contains, then choose printer. Have code just need advice.

    Hello all,
    I have the following macro on a button which was bringing up the choose printer dialog then printing when printer chosen, however now it is printing on the last printer used and then bringing up the dialog. How can I correct this?

    I want to press the button, choose the printer, then print. It only prints sheets that have "YES" in cell E1.

    Please Login or Register  to view this content.
    I also want to add this formula, which puts the value in A2 in the header. How can I build this into the code above?

    Please Login or Register  to view this content.
    What I ideally want is it to put the value from Sheet'Menu' Range A2 into the header of all sheets. I've also noticed that if something in A2 changes, the macro doesn't replace a previous entry, it keeps it and writes over it. Is there a way to get it to clear the header before running this bit of code - so it has a blank canvas.

    Any advice appreciated!

    Cheers

    Luke

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

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    Untested, but maybe:

    Please Login or Register  to view this content.
    _________________
    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
    Registered User
    Join Date
    08-09-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    Try this:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    This works perfectly for print!

    Is there a way to make it print out straight after Application.Dialogs(xlDialogPrinterSetup).Show?
    Last edited by Cutter; 08-10-2012 at 08:02 AM. Reason: Removed whole post quote

  5. #5
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    Also, is there a way to change the formatting in the header so it is font TwCen, bold, Size 14?

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

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    My version pops the dialog box first, then does the printouts automatically.

  7. #7
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    Quote Originally Posted by JBeaucaire View Post
    My version pops the dialog box first, then does the printouts automatically.
    Thank you JB. Is there a way for your code to combine the printed sheets into one print job?

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

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    I fear you are not reading these macros carefully and absorbing what they are doing and when/how. The .Show is a single line of code, certainly you can see in my macro where that occurs, and then where my macro prints the jobs one sheet at a time.

    Certainly you can see in the other macro where those codes are and move them appropriately? This is hard to read at first, but you can do it. Post up your working successful macro, or post what you've tried if it fails.

  9. #9
    Forum Contributor
    Join Date
    07-30-2012
    Location
    Leeds
    MS-Off Ver
    Excel 2013
    Posts
    177

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    Quote Originally Posted by JBeaucaire View Post
    I fear you are not reading these macros carefully and absorbing what they are doing and when/how.
    Fair comment!

    I tried:
    Please Login or Register  to view this content.
    I am still very new to VB (one week!), I'm stuggling to identify what line of code in vfxd's groups it into one print job?
    Last edited by JBeaucaire; 08-10-2012 at 08:40 PM. Reason: removed duplicate post info

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

    Re: VBA for print if cell contains, then choose printer. Have code just need advice.

    You're trying to use My macro to do what his does. Mine doesn't do that. Mine is a good example of doing the dialog show first, which you seem to have gotten.

    Now, I would say you use the rest of his macro, not mine. Plus, I see there is a new line of code needed, his code "selects" all the sheets, the added line now prints them all at once with the printer you've already selected:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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