Excel VBA Basics #31 Send Sheets Straight to Printer using PrintOut Method

Rather than triggering a print preview, sometimes you'll just want your report or sheet to go straight to the printer. With this trick, you can set it up on a worksheet, userform or in your actual report code, but you can send how many copies of whatever you want straight to the printer without any extra unnecessary clicks. Check it out!!

**Limited Offer** Learn to Make Excel Do Your Work For You with The Ultimate Excel Programmer Course – Get the Full 9+ Hour Premium Course for 75% off using coupon code: https://www.udemy.com/ultimate-excel-... Click Now to Order and get Lifetime Access to Course, Workbooks, Updates and Support!
------------------------
Create Your Own Barcode Lookup System Using Excel VBA. Learn to Make Your Own Barcode Labels the Easy way and have fun with Barcode Scanners to Automate your Workflow! In this project-driven Course, you’ll learn to Build your own Custom Inventory System with Step-By-Step video instructions. This goes in depth into some advanced Userform strategies that you can use for your other projects to come. A great weekend study! YouTube Subscibers only pay one forth! Get your 75% off Coupon here: https://www.udemy.com/barcodes-excelv...
------------------------
Too busy to Learn Piano? Use my Secrets and Play Your Favorite Songs Today! Did you know; You can learn 4 basic chords in about 5 minutes and instantly be able to play millions of songs. Seriously, check this course out. 75% off for my YouTube Friends! Click here: https://www.udemy.com/play-piano-by-e...

For Articles, Updates and to Contact me for Consulting or just to drop me a line, please visit http://www.ExcelVbaIsFun.com

Here is my link to download my FREE workbooks, past present and future: https://www.dropbox.com/sh/nwc5trm2xw...

------------------------
Playlists:
Excel Vba Basics http://www.youtube.com/watch?v=AIhKNN...
Tips N Tricks https://www.youtube.com/playlist?list...
Userforms https://www.youtube.com/playlist?list...
Sample Excel Programs https://www.youtube.com/playlist?list...
Events Series https://www.youtube.com/playlist?list...
Activex Controls https://www.youtube.com/playlist?list...
------------------------

Grab an Excel Nerd T-Shirt or Hoodie:
http://ExcelVbaIsFun.spreadshirt.com/

------------------------
Interact and Follow me on:
LinkedIn: http://www.linkedin.com/in/danielcstrong
Facebook: https://www.facebook.com/Excelvbaisfun/
Twitter: https://twitter.com/ExcelVbaIsFun
Google Plus+: http://www.google.com/+ExcelVbaIsFun


'Royalty Free Music by http://audiomicro.com/royalty-free-mu...

---

Closed Caption:

and there youtubers thanks for tuning
the dance strong with excel vba as fun
as you can see here I've got a sample
contract here and I've laid out pretty
clearly on this the blank day of blank
2013 I swear the following is true and
correct the best of my knowledge and
this can be handwritten here but
my dog ate my homework signed and
witnessed and probably dated why not
ok it's a ridiculous contract or
agreement or affidavit or whatever but
for for what we're doing today we're
going to do a print out so we're going
to make it to where they're you know
everybody knows you can do a button or
userform that makes it do a print
preview we've shown you do that it's
using the dot print preview method so i
want to show you a really cool trick
here called the printout try and do it
with the userform and i'll show you how
to do it and worksheets the same concept
so I'm gonna hit alt f 11 i'm going to
create a new user forum and very simply
i'm going to have a text box here
and label for that one throw a button on
there and it's going to say print and
then of course all have one just to be
polite this is print preview case they
don't print immediately
we'll say it's a print now
all right i'm gonna hit f4 there are
properties window
we're going to do diligence in and you
know friend preview you see me name
buttons a lot i'm sure it
oopsie in the wrong buttons here
click there BTW and print now name the
text box TV for text box and we'll call
this number copies of copies
and you guessed it for the label we're
going to say number of copies so that's
only for the printout method if you're
doing a print preview then obviously
you're going to go on to select however
many copies that you need so with the
print out method you don't really have
as many options as you do when you get
to the print preview and resize it and
you know put this and that from the
printer drivers or settings or whatever
you don't you just send it straight to
the printer as is it was pretty cool is
pretty useful sometimes you just you
know you're going to want to do that you
don't want to click here and click there
and click OK from a print preview just
wanted to print to the printer and move
on with your job so that's what we're
going to do of course of course of
course with a print preview we're going
to handle that really quickly
whenever that button is pressed we're
going to say active sheet or not going
to declare and mess with you know
specific sheet accuracy . print preview
that's your first one so let's test that
out click on double click on either
using farming hit the play button have a
pretty active sheet that previous gonna
do this
now it takes a little while for masters
computer to access the downstairs
printer
oh here's a really good really good
lesson to learn something I keep
forgetting to do so i can't access my
print preview
I can't even close the workbook I can't
close anything because it's stuck on
this so what you want to do is put me
down hide or userform one . hide or
whatever i'm gonna hold the Escape key
down and hopefully that will bail me out
the situation or maybe i can select the
ya can select this let's see
we're gonna have to get out of the
situation
well that was a tough lesson to learn i
just had to go into the task manager and
actually exit excel i thought there was
a way out of that but that that kind of
stinks so let's see if it saved anything
that we just worked on
well I'd love that whoo let's see here
yeah
whoopsie
alright so let's look and see
yeah
no did not save that that's it
okay well I'm going to pause while i
recreate all that good step a lesson
learned right so you need to make sure
that you you do the userform . hide
because once you are a print preview he
locks up so that was my bad i should
have saved before I click the button so
anyway on moment and work back i'm going
to double click on the print preview and
we're going to say active active sheet
print preview but before that we're
going to say me . hide if you're in a
different user forum you could reference
this by saying userform one . Heidi you
can do that now but since we're
currently in that userform writing code
for that user or we can say me . hide or
me . whatever that being said let's go
ahead and hit five and run that if i
click the preview button it will hide
this form first and then it will do
print preview crap i lost my sample
contract too
well do we see
you fakin
yeah
just make something up again one ok
we're back we got our sample contract
your I swear that I don't know how to
read signed witness date okay yeah let's
get back to our form and all def level
and the see the play button here so the
Print Preview you really don't need to
put any number of copies their way that
black print preview simply brings up a
preview and in this case thank God it is
not locking up it just loads your print
preview here you can close that as you
want
now the other button about the print now
this is where it gets fun
you're going to say application . print
out
excuse me active sheet . print out and
if you space
see it doesn't tell you need you to
maybe we will declare this just so we
can see what all we need to do there so
we're gonna say yes sheet has a
worksheet and set his sheet is she one
basically this worked out sheets she is
declared so we can say sheet1 dog print
out a perfect there it is
I had a space it tells me all the little
clues that I need to know which I like
like all the clues I can get that the
main things that i want to focus on are
the copies
and you can choose which printer print
file all these different options that I
do not personally use you could i'm just
going to say NBC you can either choose
on these features of the block comma
block comma you know so if you only
wanted to do copies you do comma comma
and years and then number of copies
would be safe or but I don't like to do
the commons and everything I type what I
want COP IES use a Tolan and then equals
and then you can zoom to the whichever
features of these that you want without
having to do all these comas to get to
their so instead of common come come
come come to get to hear you just put
print to file colon equals and you could
do that feature of that without all the
colors copies equals and what is the
copies going to be equal to how about
the nut whatever number is in me . text
box number copies
yes so it may or may not work why
because this is going to be text and not
considered number so let's see what
happens
I'm gonna hit f8
our halftime i'm going to put the number
one in here and i'm going to click print
now
actually let me cancel that probably to
be safe let's do me . hide because i
don't want to lock up again ominous
click save as well so hit 25 loads these
reform let's do one copy and hit print
now and
yeah so that your phone could not be
printed due to an error on the
see
alright so yeah it probably doesn't like
that let's do that the copies equals
media TV number of copies plus 0 that
will convert that to a number the number
one instead of one with quotes around it
which is considered text and not number
maybe that maybe that will make it
happen may have five see if that works
it's not like in that
I've been having trouble with my printer
ok guys i promise you if you had a
printer that wasn't as crappy as mine
this works all the time as you can see
with just the Print Preview that
whatever click print preview it takes
several seconds to load so it is it's
not the procedure is my printer so I
apologize that my printer takes forever
to load and there's some kind of memory
issue there because the printers
downstairs and it's running through
another computer that being said I
promise you that the dot print out
feature of a worksheet works and anyway
there's another thing that I like to do
sometimes I will see print now
you know i'll say maybe that's over here
and i'll say $MONEY copies
ok so let's say they put four right
there and I'll say let me just merge
these two
say print now
to border on there so that almost looks
like a button or you could make a button
and it would do the applicant you know
this sheet . print out and copies equals
sell h6 basically whatever numbers in h6
so i can show you how to do that now
forget that let's just make a pretty
button like we've been talking about i
just clicked off da to erase everything
insert shape
let's pick a square one and we'll say
print now home ribbon center center
ok so we're going to sign our macro
after we write one alternative that's
right macro
let's insert a user for oops let's
insert module we're going to call this
print out
sheet1 for lack of a better name
so let's copy seven this to declaration
stuff here
that we can copy most of that
ok she don't need to hide anything in a
module sheet1 . print out copies equals
and that is going to be sheet1 . range
and that is the range that was h6 and
cold in parentheses and should be golden
so that's the printout method i hope
that helped somebody who doesn't have a
crappy printer like I have been assigned
macro data thing the printout sh1 and I
don't want for practice and one copy so
it might bug out again I don't know
it may not connect it says its accessing
the printer
ok so maybe the computer's not turned on
downstairs or something whatever any
rate this does work and there's lots of
documentation and i use it on several
workbooks at work so have promised it
works anyway thanks for watching a
blessed case