in this video I'm going to show you how
to use a macro to create a master list
of documents here i have a basic quote
so we have the quote number and we'll
put any number that a customer and the
date and then we're gonna the prices for
parts labor and then a total now we'd
like to have this list of all our quotes
going on to one master list and I've
made basically a master list quote
sheet that has baked quote number
customer quote price quote date and
basically it has everything that you
want to record on your quotes so i'm
gonna create a button on my sheet so
that it will automatically record quotes
in this master list so what I'm going to
do is I'm going to my Developer tab here
and from the toolbox the drop-down list
here i am going to select the button and
i'm going to insert the button in the
bottom here and so then i'm going to
right click this button and i'm going to
go to properties and i'm going to change
the caption from a command button 1 i'm
going to change it to record in q list
and so then we'll close this so let's
say it's some quote number 75 will call
the customer brown y brown
inc and let's say the date is your ones
02 2017 and so let's put some parts the
parts are a hundred fifty dollars and
the labor would probably 300 that's a
hundred fifty dollars and so there's our
total price so that this is what we need
to bring over to our list we need 75 is
as a quote number the brown ink as a
customer a date and the price so we want
to record those in our list so let's
double-click on our button here and then
we go to our command button button macro
and so what we're going to do here is
first of all is we're going to define
some variables so I'm gonna put some
extra space in here and then I'm going
to go dim and is a quote as strength
and then I'm gonna define going to go
game customer as straight define price
as strength and i'm going to go to find
our dim to date which is at being the
quote date as string
okay i'm going to go dim erow and i'm
defining my erow and erow as long and
give
my data as worksheet
I'm sorry workbook
ok so the next step I i always do it in
select the sheet and the cell and don't
ask me why but it doesn't work for me
just doing selecting the sheet 1 so
let's go worksheet quotation marks
sheet1 quotation marks and bracket and
we're going to go dot select and then
we're going to go quote making sure I
mind cases the same as how i've defined
it up in the top and we're going to go
range now if we go back we can check
this out and are called number is range
be too and so we're going to go range
and then we're going to go
in quotation marks and the brackets ok
and then we're going to go to copy this
worksheet again like I said it's
supposed to work without it but I do it
every time and it seems to work better
for me this way and then i'm going to go
on customer equals range too and i'm
going to go worksheet select control v
Qdate
g2 and and I'm gonna go price and
the last one right now we define our
variables so i'm going to do next things
on your set my own set my sheet equals
looked look
open
ok and then now you have to define where
were the sheet that you're going to put
it into is so my trick to this is you
actually go to your go to your file then
what i'm going to do is when you click
on the file folder and I'm going to get
the actual address to where my file is
located so i'm going to copy that and
then i'm going to go back to my visual
basic and i'm going to put a quotation
mark and i'm going to put that in and
then i'm going to put another backslash
and then i'm going to put by the name of
my file which is master quote list
and then i'm going to put in
the quotation mark in the bracket so
that has defined where are the sheet
that we're going to enter the stuff in
is located and now i'm going to go work
sheet sheet 1 .select
now i'm going to go worksheet sheet1
start range and i'm going to go to range
thoughts go to the top right so I'm
going to start at the top so i can start
where I'm going
okay and the next thing I'm defined as
my and row so my endrow is the end of
the active the active sheet right so the
active she is were worthy of the data
ends so i'm going to go erow equals
worksheets and i'm going to go quotation
mark sheet1 quotation marks end of it
dot range and i started the top
now current region . rows .count
good now I'm gonna do it with statement
so with worksheet and now i'm going to
offset to put my data in here so let me
scroll this up so we can see it so i'm
going to go dot offset now the rows is
going to be at the intro for the offset
is going to be the end row and we'll and the
first plate and the first one I'm not
going to move it also i'm just going to
go zero to offset vertically and I'm
gonna go equals quote and then I'm going
to go and I'm gonna go paste it and so
I'm going to go offset one and i'm going
to call this one customer and then i'm
going to paste it again and I'm going to
offset it too and i'm going to call this
one price
and one more time and i'm going to call
this the last one
q date so I am going to end my with
going to save my my master sheet which
is my data now now let's go and we have
an end sub at the end there
ok ok so now I've gone back to my my
master sheet here and then I'm and click
on record in list so there is my master
sheet now it has the code number of 75
it has the brown ink it has the foreign
and fifty dollars and it has the date of
the the quotation being made so that's
how you record your documents in a
master list using a macro please
subscribe