Excel VBA - Invoice Generator - Microsoft Excel 2010 - Part 5

http://www.onlinepclearning.com
Excel VBA - Invoice Generator - Microsoft Excel 2010 - Transferring the data with some Excel VBA code. This is a Microsoft Excel VBA project that creates an Invoice Generator that is free to use. The data is stored in 2 sheets (databases) and filter to your criteria. The purpose of this project is to help with your VBA and general Excel skills in basic application development. The template is for Excel 2010 however this application will run fine if created in Microsoft Excel 2003 /2007.
I will be adding the videos in stages. First look at the Overview video to see if this is something that you may be interested in. Please feel free to contact me if you have any suggestions or problem through the comments at the end of this blog or with the contact form in this website.VBA Excel -- Invoice Generator tutorial for Microsoft Excel 2010 / Excel 2007 / Excel 2003

---

Closed Caption:

greetings this is tripp from online pc
learning calm in this tutorial we're
going to add the code that moves the
information from their invoice sheet
over into the two data lists so we're
just going to invoice and what we're
going to do we know we have a dynamic
named ryan that takes up the data here
we put that quantity in here pick up the
category put in the description and
everything sent across and we have a
dynamic name around here and a standing
named range here for a summary these 29
ranges we need to move from there and
put them into first of all this area
here for our items and then secondly
into here for a summary so that's what
we're going to do with this tutorial now
when you go to the website you're going
to find the code that we have ready for
you to paste straight into the VI editor
in your excel.application just before it
paste this code in we need to create a
nine Rines that we can use to clear all
the data now where you see the blue here
see the light blue all of that is where
we're committed to enter data the rest
of it in the white cells is all
calculated by formulas so click into the
blue and highlight the area while
holding down the control key scroll over
all the areas right through there and
through here then go into the static
named range here or the name box and I
want you to type in clear invoice and
hit enter so that you see
clearing voice comes up when you click
clear invoice it should highlight all of
those cells for you because we'll be
calling on that static named range
giving it a value of zero to clear all
the values out after we move them over
alright so now let's open up the VI
editor hit your old key down and hit 11
at the same time to expand this to full
screen in the baby is edited this a
module called copy to double click on
copy to open it and in today I want you
to grab from the website the form of the
colored for copy sells the code for
clear invoice in the code for copy
credit the other pieces of information
we're going to be working with in this
tutorial so just click into their right
click and paste them in as you see them
in here now don't be frightened by the
size of this kind it really there's not
a lot in here that's actually doing any
work in fact the only part of this code
that's doing the work is this little bit
here that basically is just copying
those two ranges aerodynamic named range
invoice and arrange accounts and pasting
them over in to their destination sheets
now I want to spend a moment with you
and go through this code because you're
probably going to want to change it to
see your needs
and really written in a lot of nights so
where you see an apostrophe or a single
quote that means this kind won't be
executed but rather you can read it so
we've written here what the code will do
it will pick up multiple rounds and Adam
22 sheets without selecting the diet
then what we've done as we put in an
error handler and the error handler it
says on error goto we've written
scooby-doo you can change screaming or
whatever you want you can use whatever
name you want then we put in the code
here to unprotect the sheet and protect
their sheet and for now we'll just
ignore that because we'll come back to
that in a later tutorial will comment
that out for the moment
next we see the to dim statements or the
dimension statements for the destination
and source range 1 and destination and
source rights too
so what this is here this set d strng
that's where we're going to paste the
information to sociate five range be
five is our first destination range and
here next one es tu RN g2 is shaped 690
fine
so then the destination ranges says
variables and because we don't want this
to happen one of the piece at a time we
wanted to all happen together we're
going to put in application screen
updating equals false that will mean
that all this code will be held in
memory until after we executed and then
it will occur following that this is
just to help us here this is just
basically saying that if i'll show you
what it's saying if these three cells
here this cell this cell or that sell
empty then give a message to remind us
to put something into those cells so
that's all that's doing they're saying
if range and 13 is empty of range and 14
and if range g12 is empty then give the
appropriate message exit the sub so we
don't execute don't go any further if
they're not empty
we're saying else go on and execute this
son
so everything's right at this stage
we're still going to give the user a
chance to exit to make sure that his
information is right so we have a
message box put in here with just a
yes/no in it so we're saying check
everything before you proceed
are you sure you want to go on and they
can choose us enough they choose know
when it's going to exit the sub for them
all right now simply put we go to our
first little section when I said this is
the heart of it in fact we don't need or
what I mentioned earlier you can just
run it on this so basically what it's
saying is said source range 1 equal
sheet2 range invoice which is a dynamic
named range then it's saying
copy that range then go to wear first
destination range down the bottom come
right up to you find the first cell with
information offset one and then placed
in the values from that dynamic nine
range once you've done that saying go to
the second source range called accounts
that's a static named range
copy that and do the same on the second
destination range which is the next
sheet where accounts will be down find
the first cell with data offset one and
then placed in the values
the next thing we look at here is to add
one value to an invoice number because
once we've had one invoice number for
all of this information we want there to
be a new invoice number for the next
so we're saying range and 14 which is
where invoice number is equals range in
14 . value plus 1 so we just
incrementing it by one right and then
we're emptying the clipboard so we're
just saying application.cutcopymode
equals false so that we can I don't have
those showing the clipboard being active
and finally when it's all completed
we're happy with everything we're going
to get a confirmation message at the end
of it all it's saying your invoice has
been sent to invoice summary a new line
that's all baby crls means and the
titles have been sent to accounts and
then we execute clear invoice which is a
little bit of code i'll show you in a
moment and then we end if
and that's the end of it now if we
strike an error during this the air
handlers going to take us down here
remember one of the top we had an error
handler that said on Air ago to
scooby-doo
well if there's an era it's gonna skip
everything else and then it's going to
take us down here to scooby-doo and then
there's going to be a message box it
comes up and says something's gone wrong
you can change this message box to
whatever you want and then it we just
end as some
all right now we called in here clear
invoice remember that just over here
here is the clear invoice it's just
basically setting a variable called see
how i equals reins to arrange clear
invoice or sheet to arrange clear
invoice that's the static named range
you created just a minute ago and then
we're saying make that value equal to
nothing
now many of you who do a little bit of
coding or even a lot might be saying why
didn't you use . clear contents
well the reason for that is there's
merge cells in there and you can't clear
both merge cells and unmarried cells
together but you can set the value to 0
and that's what I've done here right
that's that's it in a nutshell now and
next piece of code which is copy credit
is for our interface sheet that copies
the credits over now it's exactly the
same as the kind i just mentioned here
even uses the same message box
it even has the same if the ranges on
empty give me a notification but it's a
little bit different when it comes to
this because we're not copying to a
continuous area we want to drop these
cells in two different locations so
we're basically just setting one
destination range is a destination range
at sheetz 685 and then it says copy sell
our for value and then do the end up and
paste it in offset one then it says copy
r5 at this time united we just going to
the same row that going over
two columns and pasting it in next time
are copying our six going to the same
row over six columns and pasting it in
and then we're making sure that that
static named range for 26 on sorry not
the static named range the the three
cells that we've copied we're making
sure that they have a value of zero as
well and then of course it's just simply
a message box to say that we've
accomplished and deed the macro is
working fine now all of this code will
be on the website if you have any
problem you can contact me
it's pretty basic stuff really but it's
good to get your teeth into something
like this and see how it works on your
application so let's go over and see how
it will work so we just minimize that go
back into our application and where you
see create invoice right click on that
shape go a sign macro and Inter hear
what I want you to do is click on copy
cells and go okay that's going to send
that information over so now if we were
to click let's put some garden here 56
are we going to have 56 suits
and they large white owl seats have four
ties let's put some ties in just want
some information he really is that ties
purple size nine tires or I happy with
that
now watch what happens when we click the
button it's saying it appears you've
forgotten to add the date
alright so we'll go back in and put in
the date think the third 2013 and we
might put in an order number two let's
click the button again add the company
we haven't added in the company so here
we need to make sure we put in the
company that's going to pay for this or
wear suits now let's see what happens
it says everything's pass through this
level and it says you're about to
finalize the invoice just do a check to
make sure you're happy with this before
you proceed and we say yes now it's
saying we didn't have the shape and
assign your invoice has been sent to the
invoice summary and the titles have been
sent to accounts we got ok let's go and
have a look at this information so its
first of all go to their purchases and
there's the information from aaron voice
sheet all the items that we need and
we'll go to accounts and there's the
summary of items I discount no credit
and there's the amount everything works
beautifully
well I'm so that's the first that's how
we're going to move our information I we
just put our customer in will select
their quantity
select that category put in one more
here
whatever it might be and then we send
the invoice create invoice appears you
forgotten that light again us the third
into the third again and we don't have
to put in an order number but we'll put
in an order number and we'll create the
invoice you about the finalizer yes
invoices being sent that the case yet
now we've got to there it is there and
he is our next invoice alright there's
just one more thing we want to do in
this tutorial by to the interface sheet
and that other macro that we put in
there was called copy credit now what
that is for is for this invoice sheet
here it's said that when someone makes a
payment after the died of the invoice or
after invoices being published you can
add those credits into the database so
we'll put in the fourteenth of the third
2013 here and we'll see the customer was
the same customer who ordered that
always suits and the credit that they
put in was let's say four thousand
dollars they were added
alright so there's a credit let's add
that credit into the account sheet so we
go ad credits says you have to finalize
a credit check it now and get out of it
if you want to say yes now saying the
credit has been added
let's go into work camps and have a look
so now we goin here we can see that we
have two companies if their daughter
added two invoices and here this first
one now has credit as well and when we
do have filtering of the data and
summarizing realize what and when this
will be very important for us
well that's it for now that's really a
tutorial that covers all we wanted to do
here and in the next tutorial we're
going to have a look at the advanced
filters that will take us and help us to
be able to filter all the data through
here and a final tutorial we'll just
tidy up protect the sheets and so on
this is tripp from online pc learning
thanks again for joining me
I hope you found this useful so far and
bye for now