Take Tax Preparation To The Next Level With This Excel Income Tax Manager [Free Download]

hello this is randy with excel for freelancers 
and welcome to the income tax manager no more   tax headaches at the end of the year i'm going to 
show you how to create this incredible tax manager   unlimited document type uploads an automated 
tax summary complete with zip and email function   zipping all of your tax documents up in a single 
one click it's gonna be an incredible training   we're gonna do it from scratch every step of 
the way i cannot wait so let's get started all right thanks so much for joining me i've 
got a really great training this week taxes are   always a headache i don't know about you but for 
me they are getting all the documents together all   the forms the records filling everything out 
putting all those receipts in a single place   this application is going to make it a lot easier 
not only that i'm going to show you how to make it   we're going to be able to upload any document type 
we're going to be able to save it categorize it   automatically create their own organized folders 
on a per year basis so everything's going to be   organized automatically by vba and of course 
we're going to wrap it all up in just a single   summary we can get a report and of course zip and 
email that summary in just a single click to your   account so we've got a lot to show you today i 
want to get right to it i create these trainings   for you each and every tuesday absolutely free if 
you want this application of course that is free   too all you need to do is click the links down 
in the description and you get that either with   your email or with your facebook messenger we're 
going to get that sent over to you right away as   mentioned it is free however if you do like to 
support us and support this channel there are so   many great ways to do that one is you can join 
us on patreon because that way it gives you   the opportunity on that platform to get whatever 
feature you want added to this application maybe   you want me to fix something up or maybe you want 
me to focus on a section we do that inside patreon   with a whole lot more along with free pdf code 
books we have tons more when you sign up including   brand new lessons way before everybody else does 
days before everybody else does plus you'll get   exclusive deals you're going to get tons more 
including that so patreon is a great platform   for you to join us on i hope you'll join us there 
because that's where there's a lot going on and   of course i'm answering questions and comments 
there as well all right so let's get started on   this training this is a sample we're going to be 
putting this away and we're going to be starting   from scratch so you got an idea of what we're 
going to be building all right so let me move this   aside here and we're going to start on this blank 
sheet here this is our income tax so this is what   we're going to be creating from scratch before 
we do that just a little bit of an overview of   what we're going to be creating we've got an admin 
screen adding screens allow us to do a few things   one we've got a folder if we're going to be 
saving those documents in a specific folder and   categorizing them we've got to know what folder to 
save them in so we're going to create an exclusive   folder called documents right and it is this 
folder that we're going to browse for inside this   so we'll browse for that and then what we're going 
to do is we're going to put that folder right here   we also want to know what kind of form types are 
we creating we want to categorize our forms our   documents or our record into particular categories 
it's going to make it a lot easier so we have our   income form types and i've got a list of different 
income for types of course this is for generally   for us but you can change it for any country and 
then just a little description to remind you what   it is for we've also got expense types this is 
going to help us determine that profit and loss   for your or your taxable income for the year 
if we have expense types and of course we have   other form types that don't have anything to do 
with the income or the expense or write-offs and   then we have a list of tax years so our admin 
is relatively simple and then all we have is a   database and that's going to track everything else 
we've got a document id a tax year the transaction   type that we just went over in the admin the 
document form time a path right where is that   document located what is the name of it who issued 
the document the amount the notes and the row   we're also going to use some data mapping here to 
help us with that so let's go ahead and build out   this screen first and then what we'll do is we'll 
get into the code and i'll walk you through every   single step so if you're new to coding this will 
be a great training for you what i'm going to do   is i'm going to drop this down so we can see both 
the tabs and the commands here and of course i'm   going to be using the newest excel for this one 
as we'll be doing moving forward for the most part   but i'll make sure to use any features that could 
be used with any version of excel so i'm going to   color those first two columns in gray those are 
going to be for our admin and then what i'll do   is i'll give it that top row just just all the 
way up until let's go all the way across here   just to a nice i think too az should be sufficient 
now in this what i want to do is i want to give   this a color so i'm going to format that cells and 
what we'll do is we'll give it a little that green   that you saw that green fill but it'll do a fill 
effects and we'll do using two colors so i'm going   to use the the green as you saw it's going to be 
green through so i'm going to go with this mid   green to this lighter green and then that's what 
we're going to use for our top header and then the   secondary line here what we'll do is i'll go again 
all the way to let's just say a z one more time   and then a little bit of more of a fade effect so 
sorry it's off the screen formatting those cells   once again using the fill effects and then this 
time we're going to use that lighter to green   and we're going to create our background color 
which is our that light green so we're going to   blend into that then what we'll do is we'll just 
temporarily we'll color everything and then on   down all the way down here to probably let's 
just go with a z one more time and down here   okay so we've got enough rows that we're going 
to be covering that's going to be sufficient   and i'll just give it that background green color 
okay so we're sufficient we've got our background   color here now what i'd like to do is i'd like to 
have a tax year here and i'd like to have a title   so let's insert that title as you saw so we'll 
do is we'll insert right now we want to insert   a shape so we're going to put inside this shape 
here probably a type of text box so that's all   we need and we'll call it illustrations and then 
we just need a simple shape for text box here and   then that's going to be our title we're going 
to call this as you start income tax manager   and then we'll go we'll format that a little 
bit so we don't want to fill and we don't want   to border on this so we'll remove the fill we 
have no fill on that here and then we want no   outline on that so no border on that and then what 
we'll do is we'll give it a larger font probably   something around 30 pixels and then we want to 
change the font to something which is standard   i'll often like arial round bold mt okay i like 
that let's give it a little bit of the dark green   color for theme and maybe we'll just do a little 
bit of a shadow on that so it gives a nice effect   on that so that's sufficient but that's too 
much so i'm going to go into the shadow options   and what i'm going to choose is it's going to 
start out with this preset and then we'll just   drop it down a little bit less than that so it 
doesn't give it that just a little bit less so   that's kind of a nice look okay that is sufficient 
right there and what i'm going to do is just   center that and then we'll add an icon at the 
mall i'll add all my icons at the same time   so what i'd like to have is an a year column as 
you saw in the sample if you didn't no problem   and then i want to have a documents column so our 
year column probably going to start out we'll call   it a tax year that's going to be in three and four 
so what i'm going to do is i'm going to take this   and i'm going to merge and center that and then 
i'm going to give that also a fill format sorry   it's off the screen formatting those cells 
and then again i wanted to use the same   fill formats here you again using that dark 
green all the way to the lightest green okay   that's going to be because then we've got two 
different rows that we're going to compensate for   and we're going to call this tax 
year so this is going to be our tax   year and then of course i want to wrap that text 
around i want to give it a bold and then give it   our dark green color so we it's according to our 
theme and then i'll just wrap the borders around   that so we're going to format of course we can 
format in different ways the border color we're   going to use again we'll use that dark theme all 
the way around giving it so that's going to be   our tax year there in three and four and what i 
also want to do is let's increase this row here   so we have enough and then bring this up here 
so we've got enough for our both our button sets   which are going to be below this and then our row 
2.

So that's going to be sufficient for that and   then what i want to do is i also want to create 
some documents here so i'm going to do documents   and then here i want the name of the document 
and then i want the type of the document here   and that's going to be sufficient that's going 
to go into g okay so we've got the name and the   type of document we're going to center everything 
and then i want to put borders around here so i'm   going to merge and center this here and then 
what we'll do is we'll use the borders around   everything those that same green consistent border 
of course we can color that border here great we   can give it a specific line color let's zoom up 
on that so we can scroll up on that so we can give   it that border that green that line color we'll go 
with that standard green that we're going with and   i just want to color basically all these borders 
around here that's going to be sufficient and then   of course we're going to go inside we want the 
inside borders here so all the borders will color   all right i like that there now what we'll do 
is we'll format these cells and i want to give   it that a little bit darker green color so i'm 
going to do the fill effects here and give it that   darker green look that we had there the little 
fade there between these two colors there okay   and then we'll do the same thing on the lower 
one but just a little bit lighter here gives us   that nice look that's going to give us the same 
look as that merge cell on the left side which   we want want to be consistent with the colors as 
we build this application so that it gives a nice   theme and a nice color so it's appealing so what 
i'd like to do is here have all the tax years here   have all the documents here and then as i click 
the document i want those documents to appear here   so down here the row here is where i want to 
skip right have a spacer there and what i want   to put is the tax year here the tax year here and 
then that tax year is going to be here we've got   some data validation here so then next up what i 
want to do is i want to skip something and i want   to have a transaction type that transaction what 
is the type of that transaction is it income is it   expense or is it other so i want to put that here 
and then that actual transaction type is going to   go directly inside n4 here so we're going to give 
it those two just give it that white color so we   can keep track and this is going to be emerged in 
center merge center and then we'll move it to the   right all right so that's what we're going to have 
as far as our first row then what we want to have   skipping one row i want to have the form 
so put in a form or maybe the document   or the record type or it could be different 
type record type that's that named range that   we saw here so inside that i want to put 
the form type here whether it's receipt   is it another form type or is it an income right 
so it's either going to be an income form type   like here an expense form type down here or 
perhaps it's going to be an other so i've got   three different named ranges and it's going to 
be based on the form type that's selected here   and that is going to go here so we're going to 
use that on m and n6 so i'm going to merge the   center that left justify it color that white 
okay and then we'll get the formatting i'm   going to skip another row so inside 8 i want to 
have the document path what is the actual path   of that document that's very important we're 
going to need about browse for that so document   path that's going to be a longer one so we'll put 
a button here inside and so i'm going to color   all this white i want that entire path all the way 
from let's say j through m will cover that for us   so merge and center left justify and color that 
white just so we can keep track of it skipping   a row again i want the the name of the document 
document name here and i want that covered that's   going to be all the way from k all the way 
through ends because that's going to be larger   that's going to be larger so we want to contain 
it we want to make sure that we're having enough   room for our preview document here so merge 
and center that here and then left justified   that's going to be our document name and 
again we're going to do the same thing here   merge and center that and then right justify we'll 
do the borders after everything after that what do   we want i want to know who's going to issue that 
is there a vendor name or who issued it vendor   or issue or do issuer okay so who issued that 
and then also i want to have the amount i want   to know what amount is that that's very important 
obviously we need to know the amount so those are   two things that we need to cover let's merge and 
center this left justify it and then what we'll do   is all left justify this and call those white okay 
so we've got those next up all we have really left   is some notes so i'm going to do notes here and 
give the user the ability to added notes and we'll   cover a lot of rows on that so maybe we'll go from 
14 through 19 on that so let's go ahead and do   something like that and then what will merge and 
center this left and upper and then color that   white okay i like the way that that looks that's 
going to our form and i also want to put a border   around there so let's add some borders here i'm 
holding down the control what i'm going to do   is i'm going to select all of these that we just 
created we'll have to do some additional merging   and centering and things like that especially for 
some labels but we're almost finished with our on   sheet form now i'm going to format those so 
sorry it's off the screen now right click   format cells border gonna go with that same dark 
green border that we've been using consistently   i'm gonna give it a solid outside and then inside 
i'm gonna do this dashed line and then this one we   do have to fix right here so i'm going to merge 
and center that here right justify that and then   make sure we've got the border all the way around 
it here formatting those cells and then putting   that round border all the way on the let's say the 
top left and the bottom that's solid border okay   good so i like the way that looks we've got tax 
year we'll have a drop down list of all the years   we have transaction types i'll put income expense 
or other in that we'll have a data validation drop   down list based on whether it's an income expense 
here the document path i'll put a browse button   right here the document name will default that 
to whatever was used here of course the user   can change that the vendor issuer they can put 
whatever they want in here and then the amount   okay very good so let's put in some borders 
around here just as we did everywhere else   and then we're going to be good to go with the 
borders on that and what i'll do is i'll put in   probably another border just to encapsulate 
everything i'm going to insert a shape on that i   don't do they call them insulation here of course 
illustrations let's pronounce that properly then   what i'm going to do is i'm going to bring it down 
here so i want it and then we don't need very many   borders on that keep it there no fill on that 
obviously we don't want to fill we're going   to do a shape allen using consistent and let's 
take a look at that okay that looks kind of nice   and i like the way that that looks what i'll do 
is i'm going to drop this down here this is where   our button this space here is where button set's 
going to go bringing this up we don't need that   we'll put an icon up here we'll merge and center 
everything and it's looking really good so what   i want to do is i want to have tax years here 
let's say two zero one eight two zero one nine   and then so continuing down and i wanna have 
some documents here so we'll put document   and i want to have some conditional formatting 
here so we need to add in that conditional format   i want alternating rows conditional formatting 
so it's going to be based on whether there's a   value in there or not of course so i'm going to 
go all the way down here i'm going to add some   conditional formatting okay but i want alternate 
rows so we can do that using some different   conditional formatting so i'm going to add a new 
one in and then i'm going to base it on based   on two conditions so we're going to use a formula 
it's going to based on two different conditions so   equals and and then the first condition is i 
want to know to make sure that there contains   a value in d d and any row that's associated get 
rid of the absolute dollar sign just contained   d5 for any type of row does not equal empty that's 
going to be our first condition the next condition   is going to be based on whether it's an odd or 
even row we can use the mod function for that   and this is going to be for even rows so on even 
rows what i want to do is i want to put in that's   going to be a lighter color and i also want to 
put in some borders so i'm going to format that   and i'm going to do a fill but i want something 
a little bit lighter than that i was using a   previous color here so let's find it i'm going 
to use this lighter green color here now if you   want to use a previous color use the two colors 
even though we're using the same color in both   let's make sure it's the same color in both the 
top and the bottom right so that's how we get that   same color i'm going to go with the lighter color 
although it's almost exactly the same so we got   it the same color this light green that's what i 
want to show for the even rows that have the value   and i'll put a little bit of a border around 
it too so i'm going to do a format and border   and we'll do a very very light border not just 
something like like this a little bit lighter than   and we'll go all the way around the outline so 
that's going to cover what i want to do is i'm   going to copy this formula ctrl c click ok and 
i'm going to add one more new rule so i'm going   to add a new rule and then we're going to go 
into the formulas we could manage the formulas   now i want it for odd rows so i'm going to put 
in a 1 here now the odd rows what we can do is   we can keep the background color the same 
but i want to have a similar border so i'm   going to use a border here this color and 
just wrap the border around it and click   ok so this is going to be for odd rows all we're 
going to do is going to have a border and click ok   and then apply that okay so that's the way i 
like to look i like it's got a nice look in here   but you'll notice that this border disappeared 
this bottom border disappeared and this one's   going to disappear too when we add it so how do we 
avoid that well what i want to do is you can use   conditional formatting to add a border there so we 
can do if so it's very simple we can do just add   a top border on this one so we'll add a new rule 
right and it's only based on the row so it's going   to be equals row equals and it's 5 that means 
only for this row right only for row 5.

And what   do i want to do i want to put i'm going to replace 
that bottom that top border that got disappeared   when we added the conditional format all i'm do 
is adding a top border for only that row and of   course we only need to apply to that row and now 
it's there okay i like that there it looks really   good and now we'll do the same thing so what i 
want to do is i want to do the same thing for   documents but it's going to be slightly different 
so i'm going to do is i'm going to copy this   and i'm going to paste special i'm going to paste 
those formats in there then i'm going to now we've   got the conditional formatting here but we need to 
make some changes so i'm going to manage the rules   that are currently there because we just pasted 
them in this one we do need this one here that's   going to help us there and also we can also move 
it to the top make sure it's on the top and then   what we want to do is make obviously this doesn't 
apply to f5 this one is going to apply to both   f5 and g5 so i'm going to make sure it's both of 
those because i want that top border this one here   is going to be based on f5 if there's a value so 
we're going to edit that based on f right because   it's for documents f so we're going to change that 
and then this is going to apply to what it's going   to apply to f and g and all the way down let's 
say to 99 or 99 or whatever you want to do okay   and then i'm going to copy this i'm going 
to make sure that applies to is also here   and then what i'm going to do is i'm also going 
to change this to f okay there we go so now we've   got the same conditional formatting applied 
to different so clicking apply and now we can   see as we add information here you can see it's 
going to automatically apply so very good i like   that that's looking good we have our conditional 
formatting that's applied already as we add years   so what we'll do is we're going to run an advanced 
filter right i want to know all the unique years   based on this those unique years are going to come 
here through a macro and i'm going to bring those   unique years i'm going to bring them into our 
income tax and paste them right here as we refresh   the documents if we select the year i want 
all the documents associated with that year   to display there automatically okay i'm going 
to bold that and bold this one too so let's look   okay good so we've got a nice little and now 
what we want to do is we want to add some   buttons so i'm going to insert again illustration 
shapes i'm going to use this square button here   i'm going to add just a shape here create a nice 
button and then we'll go ahead and duplicate it   once we're happy with the look giving it a fill 
of our let's go with a little bit lighter green on   here we don't need an outline on here so we'll 
go to no outline and i want to do let's call   this save document very good i'm going to right 
click it here and i want to go into the format   the shape here because what i want to do is i 
want to format the properties the first thing   what i want to do is go into the text options and 
i want to do the text outline here i want to make   sure that there's obviously no outline and no 
text field the solid feels fine for our text   actually the text box here i want to make sure 
that there's no margins here or maybe a slight   one on the right margin .05 that's sufficient we 
don't need any left top or bottom okay that's good   i like that that's good and what i'm going to do 
is i'm going to right justify that because we want   that icon on the left and we want to put it in the 
middle right once we have it the way we want it   then we'll go ahead and add the icons what we 
want to do is we want to add all the buttons first   then the icons on top of that so save document 
that looks pretty good i like the way that that   looks i'm going to place that right up here on the 
left and i'm going to duplicate that using ctrl d   the next button that i want to have after that 
is i want to have new documents so we're going   to call this new document and then after that i 
want to be able to delete the document so again   i'm going to duplicate that using control d and 
this one's going to be called delete document   all right very good we'll make this one a little 
bit bigger so we can get some room for some icons   making sure i'm going to hold down that they're 
all the same heights going to the shape format   and making sure that they all have the 0.26 
height that's sufficient perfect great but i   want other buttons too so let's close this out 
what else do i want i want to obviously be able   to view the tax history so i'm going to control 
d and i want another one called view taxes review   tax and then we'll call it tax summary 
right because i want to be able to   view that tax summary when we do that everything 
else is going to be hidden and that tax summary is   going to be displayed so i'm going to maximize 
this a little bit here and then we're going to   place that directly over here so we want to be 
able to view i want that document preview to show   up here we're leaving that space for that document 
preview inside that tax summary we want to have   some additional buttons and so what are those 
buttons well the first one is going to be hide tax   summary so i'm going to control d i want to hide 
the tax summary so we'll do hide the tax summary   hi we'll just call it hide summary and that way 
they're going to give it either the ability to   show or hide we'll make that a little bit smaller 
and then i'm going to duplicate that and i want to   also be able to print the summary so print summary 
good and then the last thing of course we need   to be able to zip and email that summary so not 
only a summary but all the documents as well so   zip and email i like that that's looking pretty 
good we've got all of our button sets except for   the browse button so we're going to need that 
so i'm going to duplicate that one more time   here and place a browse button i'm going to 
place that this one will make a little bit   smaller i'm going to place that right here but of 
course we need that a little bit smaller so it's   consistent with that so i'm going to change the 
height on that to about 0.22 so i like that we'll   call this browse that we can actually browse for 
that document that we're going to load so browse   okay and then we'll change the width to that about 
point seven making sure that we have room for the   icon enough room for the icon that looks good okay 
so we've got all of our button sets and let's move   these up here zip and email and then print summary 
and then we also have the hide summary so we got   print summary zip and email all of these of course 
are going to go and i'm going to bring these let's   go ahead and add those icons now we'll put these 
here for now and then what we'll do is we'll group   them so that we can easily hide and show them 
accordingly okay so we're good to go saving   our work now we're ready to insert our icons 
associated with this application we always want   to do the buttons first and then so we're ready 
to go okay so we're going to insert the pictures   on that we can do that through here pictures and 
then of course this device now i've got some saved   pictures up and we're going to put those directly 
inside here so let's pull them up here i've got   them right up inside this folder what i'm doing is 
i'm just going to select all the icons we have i   don't think we're going to need this envelope one 
here and then what we'll do yeah we'll put them   all in and then insert those okay good and now 
i'm going to size them accordingly let's say 0.18   bring making them a little bit smaller here and 
then we'll position them accordingly around the   application so the first thing is we've got our 
screen icon here so we can make that a lot bigger   so that's going to be for our application logo 
there for taxes then what i have is we're going to   this is going to be for our new document here this 
one's going to be for our saving or updating and   that'll be sufficient for deleting it of course 
we've got a few options we can use the x or we   can use the trash can let's go with the trash can 
here and then what we want we want to be able to   view the tax summary that's important so i've got 
an icon here right here for viewing our tax enemy   we've got to be able to hide that summary so we're 
going to use a triangle for that and then also   what i want to do is i want to have the zip and 
email we're going to use for this one we're going   to use zip and email the printer of course i need 
that printer too that's going to be not a printer   there folder icon is going to go here putting that 
folder right there and then let's say i need print   and hide summary let's add those now all right 
let's browse for those additional two icons i've   got some more that i want to add inserting the 
pictures and we'll put those two hide in summary   so it's going to be this printer here and then i 
want the left arrow here that's the two additional   setting those to 0.18 okay now what we're going 
to do is we're going to just add those in here   i'm going to hide somewhere to go right in here 
and i want the print summary to go right in   here okay good so now what i'm going to do is i'm 
going to use my selection tool and make sure that   they're all horizontally vertically aligned there 
we go so now i'm ready to group these right so i'm   going to group them individually first and then as 
a group together because i want that summary group   separate right so i'm going to group this here 
and i'm going to group these these three buttons   here are going to be their own group right full 
need to be used for our summary group and we're   going to give this a name we're going to call this 
tax summary group so this group these three groups   of buttons here grouping those here and then i 
want to give those a group here call this tax sum   group and i also want to do with the other buttons 
so not only other buttons but the shape as well so   this one here i've grouped these here all the 
way already just grouping those together now   i'm going to give it a group so i want to group 
that and we're going to give that a specific name   the name that i'm going to call that is called 
document group because it's exclusive for just   those documents so i'm going to add here document 
group here okay so the next important thing to do   is holding down the control i want both of those 
groups i want to set the properties on both of   those groups to move but don't size so we're going 
to go down to properties and move but don't size   with the cells so we're setting that up this 
group i want to show only with our tax summary   so i'm going to take it over here and i only 
want to show it around right around here about a   a through a e this summary so our summary is going 
to end up right here okay here's our summary so   this is a merge and center cell here i want to 
put that summary in right here that summary here   is going to be called tax summary so i'm going 
to i've got it already merge and center just a   little bit quicker there so we're going to 
give this name called tax summary but i'm   going to put it inside a formula because i want 
that year to be dynamic so i'm going to put in   tax call this tax summary for year ending and then 
i'm going to put a space and then we're going to   put the year but not yet the year is going to take 
on a formula so we'll be adding that year in this   area this merging center cell here this is where 
our graph will going to be going then what i want   to do is i want to put in our income here so i'm 
going to put in total reported income down here   i'm going to put total expenses and credits so all 
the credits that you're going to go to remove from   your gross income and then i want total taxable 
income here so that's what i want to show here   we're going to of course make these cells white 
here because we'll they're actually i'll make   them just a light green that lighter green should 
be sufficient here because it's not a user entered   field i'm going to format those cells all the 
way around here and then what we'll do is we'll   use our of course our green consistency and we'll 
do all the way inside and outside of the borders   and then this put the dotted line on in the middle 
there i like the way that looks i'm going to put a   dotted line around line around here so formatting 
those cells and we'll keep this open so we can   format those cells accordingly all right so i like 
that and then for this area here what i'd like to   do is i like to have a border here a larger border 
up here but i also want to have the header names   up here what are those header names so i'm going 
to put in transaction type i want to know the   transaction type i also want another form or the 
document type we'll put in doc type and then next   up what i want to have is the document name what 
is the name of that document and then issuer who   issued it was it a vendor issuer issuer so put 
that we could probably put in vendor or issuer   because it could be either one whoever issued 
that could be a business it could be your company   that you work for and then lastly the amount so 
we want to have the amount in there okay let's   format these based on our current theme so we're 
going to add format those and then what i want to   do is i want to add some fill effects there just 
as we've been doing with our with our theme here   using the two color greens that we're going 
to have so i'm going to go from the mid to   the lightest there that's sufficient there and 
then i'll do give it some internal borders here   so and then i want to do a thick border let's 
do a thick border on the top and then inside   the left middle and right and the bottom we'll 
do them i like that and just around here we'll   do the thick border because i want that going to 
be printed out so i wanted to make sure it looks   nice because that also is going to be printed 
out and zipped up so you can send that to your   accountant so i think that's kind of nice so that 
they get it not only do they get all the documents   but they get the summary as well okay looking good 
and then here what i want to have is conditional   formatting based on that now that conditional 
formatting is exactly the same as the conditional   formatting we had here and here so all we need 
to do is copy and customize so ctrl c and then   we'll do is we'll paste special of course you 
can paste the formatting here as well using that   now that we have those conditional formats pasted 
in there we can go ahead and manage those rules   then we can update them accordingly so for this 
one of course it's not going to be row five   we're going to change that to row nine row nine 
we want to make sure that top order is consistent   so formatting those and it's going to apply to 
the entire top row all the way from aa so we're   going to select a9 all the way to ae 9.

Okay so 
for the next one right we want mod row but it's   not of course it's going to be the columns got to 
be adjusted 9 is correct but it's not column d we   now need to update this to a a so we'll change 
this to a a and clicking ok and then i want to   change this as well also instead of d i want to 
change this to a a and now all we need to do is   update the applies to so we'll select in one of 
them again putting down and then all we need to   do is update the last row we'll go with a high row 
say 999 that'll allow us at least for a thousand   or so okay so we're good to go on that now i need 
to just copy this and paste it down here now we've   applied those and now all we need to do is just 
check to make sure so we'll just add in some   data here and to making sure that that looks good 
that's the way i want it there looking very nice   okay very good actually let's kind of update this 
one i like this one i want to be a little bit   lighter here so i'm going to just going to manage 
that i'm going to change this dark green we've got   a lighter green i'm going to change this to white 
i want to change something to to just white so   let's go here and edit roll something a little 
bit lighter because i want to make sure that   that data i'm going to do a fill and fill is white 
here let's take a look at that see how that looks   and then we'll maybe change it i'm going to change 
this one to lighter green because i want them   because it's going to be a report i want to make 
sure that it's very easy to read and so let's go   into fill effects i want to choose the lighter we 
can choose it from here that's kind of nice okay   i like that that's good we can do this two 
color here good okay that looks nice i like that   clicking okay and then apply okay good so i want 
to make sure that it's very light and easy to read   that looks really nice continuing on so we've got 
our summary here and we've got everything ready   we're almost ready for vba and that's going to 
help us add our data we'll be adding in of course   the data as we get it in here so we've got some 
data validation here now this transaction type   is going to be more than one type right i've got 
really three different ones that could be we have   income we have expenses and we have others so 
those are the three different transaction types   that we're going to be working with so let's 
go into here and add those three into the data   validation so here we're going to add a brand 
new data validation so we'll go ahead and add   it in here clicking here data validation right i 
want three i want to list place and i want income   expenses and other okay so we'll do expense and 
other the names are very important because it is   those names that we're going to use inside name 
range so income expense and other okay now keep   in mind inside the named range i've already 
created three different lists so we're going   to go into the formulas the name manager and we 
have here we have income type we have other type   and we have expense type now each one of these are 
related to the list as using a dynamic named range   using the offset so we have form types we have 
expenses that are going to cover all the expenses   we have income that's going to cover all the 
income and of course we have other that's going   to cover all of the other here so here we can see 
all the other so it's going to be those two things   right here so what i want to do is i want to 
create a data validation so as we change this   to income expense or other this data validation 
this record change data validation list is based   on whatever it is in here so whether if we choose 
expense it's going to be all of these if we choose   other it's going to be all these and if we choose 
income it's going to be all of these now we can   do that using vba so we're going to get into that 
in just a moment so as we make that change i want   this data validation to change so we also another 
thing that we need to do with vba is we need to   know which year we need a unique list of years 
now we have some data with all the tax information   here what i want is i want a unique list of years 
based on all the data so we're going to extract   that using of course advanced filter without 
criteria and we're going to getting our unique   list of years and then it is that list of years 
that we're going to bring over inside our income   and have it populate this area here great so we've 
got that covered we understand how we're going to   build that we're going to do that with vba there's 
a few things that we want to add inside this   application some formulas and some information as 
we created not too much so what do i want i also   want to keep track of certain things on the admin 
i want to know the selected year and i also want   to know the selected document right so i want to 
know the rows of that and i also want to know the   document id each documents can have an individual 
id i also want to know what row that document is   associated with what database row and then i want 
to know the next document id okay so we want to   know all those information now these are selected 
these are going to based on conditional formatting   and what i mean is when i select it i want 
conditional format high like that so for example   if this is 6 i want conditional formatting to 
automatically show that and if this is 6 i want to   also show so these are row selected document i'm 
going to add that i want to select i'm going to   add that row to that because i want to know what's 
row selected document row making sure that that's   clear okay expanding this so we can see that and 
i'm going to left justify this that is it that's   relatively all for the admin it's relatively easy 
and then i'll just give this a unique color so   we can highlight that it's for admin use and of 
course that column is going to be hidden so we're   going to use all the borders right all let's just 
do all the borders here all right so what do we   have i want to know the document id and i want to 
know the document row now i have a named range for   document ids if we look in our database we see we 
have document ids if we go into the formula i've   already created a name range called document 
id if we look down here we see that we have   using the offset all the document ids okay so what 
do i want to have i want to know the row that's   associated that means if document id is displayed 
i want to know what's on row four therefore if uh   let's go to the right sheet here randy if document 
id here is set at one i want this document row to   show four so we're going to use equals if air we 
use the match formula and what are we looking up   i'm looking up this document id and what i'm using 
i'm using the match right obviously and then i'm   going to use the lookup array that's what i meant 
the lookup rate is going to be that document id   i'm looking it up and i want an exact match so 
i'm going to use 0.

But i want the row number i   don't want it to return 1. that row number that 
first one is going to be on row 4. so we need to   add 3. if it's not found or there's an error i 
just want to show empty okay so that means the   document row for one is on row four document two 
is on row five perfect that's exactly what i want   i also want to know the next available document id 
we can use the max formula to do that we're going   to wrap that on if iferror because if there's no 
data at all it's going to create an error so we're   going to use the max formula and the document 
id i want the max of all the document ids plus   one i want to know that next available document 
id if there's no data it will create an error   so i just want to set the default if there's no 
data to one that's the first available document id   so we know that our next available one's 29 and 
if we look inside our database and we go all the   way down to the bottom we see our last one's 28 
and that means our next one's 29 that's exactly   what we want all right let's bring this up here we 
will just show just the tabs for now and so we've   got all this information that's all we need let's 
save our work what i want to do is add just some   conditional formatting here i want to know i want 
that selected row to be highlighted in green so as   we add rows i want to know so what we're going to 
do is we're just going to go into the home and add   two more conditional formatting manage those rules 
and i want that rule to be on top so i'm going   to create a new rule here and that's going to be 
based on the row of a certain of a certain cell so   equals row and then it's going to be equal to 
what that year is going to be based on here   selected row if b2 is equal to selected raw i want 
to give it a unique format i'm going to give it a   fill effects here i'm going to go that light green 
or the middle green to very dark green and i also   want to give it a font color the font is going to 
be bold and white all right i like that that looks   good and so that's going to be sufficient click 
and ok and then apply it and making sure that   that works good that's exactly the way i want 
it i'm going to do exactly the same thing for   documents so i'm going to edit that just copy that 
make it a little bit quicker but not much quicker   create a new rule right and then use a formula 
and of course this is going to be based on the row   and it's going to be based on b3 i'm going to use 
the same exact so going to the format the fill   fill effects here the repetition helps us remember 
these things again using the same color the same   consistency here clicking ok font is going to 
be bold and the font is going to be white all   right so i like that and of course the apply 2 is 
going to have to change on that so that applies to   is going to be from our f5 through g so let's 
tab over here and update that accordingly and   then we'll just go down a bunch of rows we also 
need to go down the rows for this 999 that's a lot   and this is nine nine enough sufficient okay so 
we're going to apply that and there we go okay so   now we see as this changes right so does our 
year as this changes go to five which is our   first row it changes the year that 
row when we make a selection change   that will change i will put whatever row we've 
selected in here i'll put whatever row we've   selected in here very good i like that the rest 
will come through vba and that's exactly what   we're going to get into right now we are ready to 
go into vba and do a few things the first thing   what i want to do is when we make a selection i 
want to put that row here and then we're going   to create a macro for that so let's do some of the 
on-chip code that means the code that happens on   the sheet when we make a selection or when we make 
a change we want some things to happen so what is   that well we're going to do that inside vba we're 
going to go to the developers tab to get into vba   we're going to go into visual basic there's a 
shortcut called alt f11 that will get you there   and we can hide this code that's for our sample 
right we don't want a sample i want to focus on   our actual document they're working on so we have 
our income tax here this is the sheet we've got   three different sheets here the code names are 
admin income tax and tax document we also have   modules where we've created just a little bit 
but i've got nothing inside these macros we'll   be writing them together except for this one it's 
kind of basic and then of course we have document   macros which will handle the document and we have 
our tax summary macros we're going to be able   to show hide refresh and print and zip an email 
where i've just done some dimensions and variables   to make things a little bit quicker but what i 
want to do is focus on that on sheet code right   remember we're going to focus on on sheet and so 
the first thing what we want to do onset is we're   going to make a selection change so we're going to 
go into worksheet and then selection change right   because we want to focus on making a selection 
change on those years right if i make a selection   on anywhere from d5 on down and making sure that 
d contains a value we want something to happen so   let's write that up right now so it's based on 
a selection change right and if it's a specific   range so what is that range of course it's going 
to be from d5 all the way through let's do d99   and we make a change d99 is nothing and of course 
we need to make sure that there's a value in d   nothing and right if not intersection means we're 
actually making a selection change to that range   not and nothing cancel each other out and range 
we want to make sure that d contains a value d and   the target dot row dot value does not equal empty 
only then do we want to do something and then   what do we want to do well the first thing what i 
want to do is i want to make sure to add the word   then i want to make sure that range b2 range b2 
takes on that target row value equals target dot   row set the selected row year it's the year of the 
year row okay i want to make sure that we do that   okay so now what i want to do is i want to load 
a macro obviously we haven't written that macro   but what is the name of that macro the macro is 
going to be down here i want to load some specific   documents so how do we do that well we're going to 
create a we want to actually make sure that we run   the macro to load those documents when i select 
a year i want all the documents to load for that   year so that's going to happen now it is this 
macro right here that we have not written that   it's called documents list so it's inside the 
documents list and it's called load the document   list we also had to load the tax years so it is 
this macro nothing's in there that that we're   going to be running so we might as well write 
that in right now and just have it them and   so what this is going to do is going to run 
macro let's put the comment there run macro   to load list of documents for selected year so 
we only want documents for whatever year has been   selected i also want to make sure that we're going 
to actually add in something a little bit later on   which is going to be making sure like if i select 
a year and that summary is i want to change   that summary so let me better write it in case 
i forget so here's what i'm going to show you   if what i want to know is here right at some 
point we can get rid of that for now if i'll   comment that out for now so what i'm going to do 
is if i want to know if they select a year and we   have this summary now either we're going to have 
two choices either we're going to be displaying   this or we're going to be displaying this but not 
both at the same time right so my idea well way   too far so my idea is here if we have this 
summary visible if this summary is visible   i want to refresh the summary right i want to 
know only those particularly transactions for   the given year if this is visible right so how 
do we do that so i'm going to format this i'm   going to put the border around this i like 
that a little bit better all the way around   okay good so so what i want to do is i want 
to know is column a a visible or not i just   want to run a test if the column is hidden right 
then i want to make sure that we're not if it's   not hidden so let's add that in so basically when 
i select a year i want to load the documents here   and i also either i may want to update this 
summary if it's visible so we can do that with   some code so let's continue writing that code 
now and so we know that it'll kind of all come   into play a little later on but it's easier for us 
to write the code now and then after we come back   hey what i want to do is i just want to check 
to see if column entire column dot hidden equals   false then what do i want to do then i want to 
refresh that schedule i want to run the macro   that's going to refresh that summary of all those 
documents then then what do we want to do well   then i want to refresh it is this tax number we 
have a specific macro that's located it's going to   refresh it this is the macro that we're going to 
write so it is this macro that i want to refresh   all of them so i'm going to put that directly in 
here then and here's the explanation so refresh   yearly summary if visible okay we're just checking 
to make sure visible and that's all we need to do   that's relatively simple okay good i really 
like that that's good that's all we have to   do for that pretty simple now what else do we 
have to do while we're on selection change right   this is only if we're selecting years but we also 
want some things to happen if i select a document   right if i select a year i want so notice how 
that's changing now you see how it's changing   perfect now what about if i select the document if 
i select a document i want that document to load   right i want to load whatever document i want that 
preview to show up here of course we're going to   need more space for that right so we're going to 
need the space for that document and that's fine   that's perfect and so we're either going to show 
this section or we're going to show this section   but not both we're going to use this button to 
hide it we're going to use this button to show   it okay so that'll come into play in a little bit 
so as we are on selection change when i select   the document i want a few things to happen one i 
want the row that we selected and i want that row   to go directly inside b3 and then i want to run 
the macro that's going to load that document so   let's add that in right now so let's let's update 
this so we can just we're clear with the comments   on selection of year okay so we're good with 
that now what we also want to know on selection   of a document so let's write in a comment here 
commented on selection of document and we can   just pretty much copy and paste this and then 
update the row so i'm going to copy this here   and i'm going to update those columns right 
we're not focused on column this time we're   going to be focused on f through g f through g 
we want to make sure that f contains a value so   f through g we need to make sure that f 
contains a value but this is going to be   f through g and make sure we add the end if right 
so everything happens in between and if that what   do we want to do well that's going to be focused 
on selected row of the document so i'm going to   copy this but that's going to be based on b3 b3 
is going to take on that row and that's selected   document row selected row for document right this 
is a specific document when we select that we want   a few things to happen the first thing would be 
of course we want that target row so that we can   automatically add that conditional formatting and 
then next up we want to run the macro that's going   to load that document right we haven't written 
that macro yet but that document is going to   be right here and it's going to be called here 
document preview right we've got document preview   and we have document load so it is this document 
load here that i'm going to add in i want to load   the selected document and that's what's going to 
be here so here we can do this but i also want to   do one more thing i also want to add in whatever 
is in here i'm going to put that document id i'm   going to place that right in here so the id is 
going to be hidden it's going to go in column e   remember every single document id when we run 
that's going to have an id we have a document   name right and we have the form type so we have 
all that information but i want that document   id because it is that document id i want to place 
directly in b4 so i'm going to place that directly   in e but you won't be able to see it well if 
you will as i'm doing this but of course when   we release them you can easily make them hidden 
a few different ways so from e is going to take   on you will take our document ids when i load all 
those documents when i run the macro to load the   document id will go here in column e the name and 
the type okay so e will take on that so whatever   is in e i'm going to place directly inside b4 
so that's the first thing we're going to do   range b4 dot value equals where is it going 
to come from it's going to come from range   e and the target dot row dot value that's going 
to be our document id now once we have that i also   want to place i want to run that macro document 
load so this is going to be run macro to load   document good i like that that's good so we've got 
everything there so now of course there's nothing   here but you see there's nothing which is fine 
because once we put an actual data here perfect   so that's it for the selection change event so 
we've written all the macros for selection change   but i've got more i want to know now we're going 
to focus on a worksheet change event so i'm going   to go in here i'm going to put in change and 
now we're going to write and click ok so that's   fine now we're going to focus on some changes 
based on the worksheet that means when the user   actually makes a change right so one thing i we 
mentioned when i make a change to here and for   i want something to happen right i want the data 
validation here i want this to be either income   list expense list or other list so we've created 
three named ranges so i want to create data   validation based on that but i only want to make 
sure that kind of changes any kind of change but   making sure that it's not blank so that's going to 
be a change based on n4 so that's what we're going   to write right now and it's going to be based on 
that so changing if not then right let's add then   n4 right n4 but i also want to make sure that 
n4 contains a value so and range n for dot value   does not equal empty then i want to do something 
right we don't need two then so that's just one   and sufficient for our purposes or anytime okay so 
then what do we want to do well when it comes to   data validation the first thing we always want to 
do is delete any data validation that's in there   so m6 we're going to delete that data validation 
so that's the first thing we're going to arrange   m6 because that's the cell putting it in 
dot validation dot delete okay delete any   data validation we can't add new or update 
anything unless we first delete the existing okay   so next up we're also going to do data validation 
but this time we're going to be adding data   validation it's going to be based on what based on 
conditions we're adding three different ones what   are we going to be adding if we look in here we're 
going to be adding to either three different types   either we're adding income type writing other type 
or adding expense type now notice the names are   all very similar based on this value all we have 
to do is add the word type based on this so when i   add it it's going to be based on that don't worry 
about that we're not done with that yet we're not   done with that yet so what we're going to do is 
we're going to add it right dot add i need to add   a validation type and of course what type we're 
going to be adding we're going to be adding a list   type right so let's go down here it's going to be 
list and then what do we want to do i want to make   sure it's going to be alert style stop so we'll 
do xl valid we want to stop if it's incorrect   alert stop right we want it to stop now what are 
we going to add we're going to add a formula on to   that right so i'm going to go here and what 
is that formula we're skipping the operator   and we're going to add a formula so it's going 
to be equals and what else and i want to add   that named range but what is that named range 
that named range is based on whatever's in n4   income type expense type or other type so range n 
for dot value equals that right income expense and   what else and we need to add the word type at the 
end of it type okay good i like that let's take a   look at that now let's work it out okay let's get 
rid of that equal don't need that in there okay so   and the type so we combine the name income 
expense or other with type this is going to be   our complete named range with the equals okay good 
let's take a look at that and now so when i change   that we don't need that there all right let's 
take a look at that changing this to expenses   now we have a list of expenses let's look 
and double check our data validation here   right looking in the data here checking our 
data validation to make sure that it is accurate   and then clicking yes and then this expense 
type perfect that's exactly what i want   making the change to income here and then 
checking our data validation again making   sure that it's income and now of course when we 
go into the data and we check our data validation   merge cells it's okay and we see that it is our 
data value let's go here again check that here income type perfect so that's what we got and then 
lastly we'll double check our other here making   sure that we have it data validation making sure 
that it's different of course we can look at the   drop down list we know this is the three items of 
others so we know it's working correctly all right   very good so our data validation is working that's 
it for all of our on-sheet macros that's it for   all these macros that have to do with onsheet what 
i'd like to do now is focus on the document right   so what we want to do is some of the documents 
we need to do a bunch of things right here   so i want to know first of all our unique lists 
and we also want to get the browse folder so let's   get that in let's start out with the first one 
let's get a brand new unique list of all the years   based on of course all the data here so how do 
we do that well we're going to go inside here   inside our document macros module and tax years 
we want to load the unique list of tax years   so how do we do that well it's relatively simple 
with an advanced filter but we want to make sure   that when we're running this type of an advanced 
filter that we don't use any criteria the reason   is there's no criteria all i want to do is get 
a unique list of years and i want to place those   lists of years here so let's do that right 
here the first thing what i want to do before   i add that unique is i want to delete any or 
clear out any years that are associated here   so on this screen we're going to clear out any 
years so that's the first thing i'm going to do   income tax that's our sheet name income tax 
dot range d5 through d999 let's just do 99   dot clear contents clear any existing gears okay 
next up what i want to do i want to really focus   on the tax document database so with tax doc 
database we want to make sure we have the name   right so we're going to use the period that 
triggers intellisense and we know we've got   the sheet name right okay so moving on one thing 
we want to do is we want to clear i just want to   make sure that we're clearing any previous results 
those previous results are going to appear inside   our tax document database on columns right here 
m3 all the way on down so i'm going to clear that   out first so dot range m3 now we're focused on the 
database through m999 dot clear contents dot clear   content clear previous results once we're ready 
that i want to get the last row now that last row   is going to be equal to based on column a that's 
going to set us our last row of data last row of   data of data now i use autohotkey to automate that 
to help me a little bit faster so now if last row   is less than in this case four if that last row 
is less before that means we have no data so we're   going to exit the sub now we're ready to run our 
advanced filter but before we do that when we're   running an advanced filter with without criteria 
i want to make absolutely sure that there's no   criteria right so the best way to do that is to 
make sure that we've deleted any criteria so we   can do this with dot names and i've added on 
air resume next because if there is none then   it could create create an error so criteria dot 
delete right i want to delete any criteria delete   any criteria now if we're just using an advanced 
filter with criteria it wouldn't matter it'll   replace the existing but since this one has 
no criteria and i'll show you that in a minute   we want to make sure to delete any criteria and 
on air resume next okay so now that we have that   we're ready to run our advanced filter and i 
can automate that a little bit quicker using   auto hotkey let's set up the data let's take 
a look at the data right i'm really focused on   years right all i want is unique list of years so 
our original data is just going to be that those   tax years original data is located directly inside 
only on b3 all i want is unique list b3 so that's   all we have to do is put in b3 right our original 
data is b3 here b3 through b in the last row   no criteria right there's you know all i want 
is unique list so i'm going to clear that out   remember there's no criteria here where do we want 
to copy that to we want a unique list we're going   to copy it directly to m2 so m2 we're just 
going to change this up and just change it   to m2 we do want a unique list true that's very 
important then what i want to do is i want to   determine the last row of those results once those 
results come into m3 right i want to know the last   results row so the last results row is equal to 
what right basically it's going to be equal to   not whatever is in m so we're going to change 
this to m this is the last row of the results   last row of results i want to check to make sure 
that that those we actually have less than right   if it's less than 3x the sub that means we have 
no data if it's less than three then no data so   if last will slow s less than three then exit 
the sub if it is not less than three what i   wanna do then then i want to also sort it right i 
wanna sort it based on the first year first right   but i wanna check if there's only one row 
of data let's say that the last row is four   there's no reason to sort it right so if the last 
let's we can do that again using autohotkey a   little bit quick if the last result is less than 
four then i want to do something i want to go to   let's just call it single year and then it's going 
to skip the sort so we're going to do a single   year and so what that's going to do is it's 
going to skip the sorting we don't need to   sort if we have a single row of data but i do want 
to sort it based on the years so now we're going   to focus on that sort we've got multiple rows of 
data so with dot sort and the first thing what i   want to do is i want to clear the sort field sort 
fields dot clear clearing all those store fields   next up what i want to do is i want to add a sort 
field clear right i want to sort them based on the   year so the lowest is first so dot sort fields 
and then we'll add add a key right and what is   that key is going to be equal to it's going to be 
add let's call it key it's going to be equal to   and we've got to call out that sheet again tax 
document database because we're inside a sort   dot range m3 that's our key right and now what 
i want to do is i want to sort on i'm going to   sort on of course based on sort on value so sort 
on equals excel sort on values and then what i   want to do i want to sort it by ascending so 
order is going to be equal to xl ascending   right because you want it from the smallest 
to the left smallest to the largest ascending   okay and then last on we want to make sure i 
spell that right probably not ascending data   option equals excel normal excel normal sort 
normal right sort normal okay let's take a   look at that because usually i mess this up add 
fields tax document m3 that's our range right   that's our first sort on sort on values order 
equals ascending data option equals normal looks   pretty good okay what i want to do is i want to 
set the range that's pretty simple so we're going   to set the range of that of course it's going 
to be based on m3 in the last year again what   we want to do is we want to make sure we're 
calling out the sheet tax doc database.range   m3 of course that's our the first one through 
m and the last results row last result row okay   that's where consort and the last thing is dot 
apply okay so we're just going to apply that   there so we can apply that we're good that's 
end of the story that's all we need to do   so once all the data gets sorted then all 
i want to do is bring over those results   now i want to bring over those results whether 
it's a single year or multiple years so   we're skipping that now we're ready to bring it 
over okay so we're going to do income tax this is   where we want to bring our dot range we want to 
put it in row d5 right the first row here is d5   of our incontext all the way through d in 
the last row la and the last results row last   last result row however notice our starting 
row here is on five our row here is on three   so we need to compensate for that so i'm going to 
add two onto that dot value is equal to based on   again our dot range based on our database here 
dot m three through m and the last results row   last result row same as our range dot value 
okay so that's going to bring over all the   basically all the years okay good saving 
our work here we're going to run the macro   always want to save before we run our macros so 
we're good to go okay let's run that macro and   see what happens in running we've got our years 
here let's take a look inside our income tax here   and we've got our years here now we've got 2018 
all the way the only other thing i want to clear   that probably when i run this i probably want 
to clear the selected row probably right so   let's go ahead and clear out b2 as well and then 
we can do that all the way up here so as we add   the traction i'm going to add b2 to that right b2 
because when we restart the list we really want to   clear out that sort let's run it one more time 
here and take a look here okay take a look all   right that's that's good i like that it cleared 
out the select row i'm going to center that   all the way down here and we'll get it centered 
all right so we've got this data centered there   looking pretty good that's very nice okay so 
now what i want to do is i want to load all the   documents that are associated to that year okay so 
when i run this macro remember we've already added   that i want to load all the documents associated 
with the year so we're going to run an advanced   filter right when i click here that's exactly 
what happened i want to know what year i want   to set a criteria based on the year how do we do 
that well we can do that right here i want to set   that tax year notice the tax year is here already 
criteria based on whatever row we've selected   how do we know what row is on well here's what 
we do know we know that this is row 6 right if   we use an indirect formula and i know that we're 
going to do from column d and whatever is in 5   i can extract the year using the indirect formula 
and we're going to use that right here so if error   indirect again focus on income tax column d and 
whatever's in b2 and of course that is also going   to be using a1 style so it's true if there's an 
error show blank that is what's going to happen   as soon as we have if we have the criteria here we 
know what gear to filter by we can take all this   data this is just some extra data we don't need 
that here we can take all this data here and then   what i want to do is i want to know all of the 
documents associated in that specific year i   want to put the results in right here then i want 
to bring all this information over and i want to   bring it directly remember i said the document id 
is going to go be hidden kind of and it's going to   go in column e the document name and the document 
type so again document id document name document   type bringing all that information in okay so 
let's write a macro to do that right now that's   going to be next up on our mac or list it's called 
document loads list so that's what we're going to   do right now so let's set that up here so how we 
going to do that well the first thing we're going   to do is again we're going to do clearing out 
all the information just as we did before so what   i can do is just copy that here and then update 
the let's do this i'm going to copy this one here   and then we're going to update this so i'm going 
to instead of course b2 it's going to be b3 i   also want to clear out any selected document row 
based on b3 but i also want to clear out all the   documents that are that's going to be located from 
e starting in that e all the way through g right   so again what i want to do is i want to clear out 
any when i'm reloading this list i want to start   out clearing that document id all the way starting 
at e5 all the way through g and clearing it out   okay clearing out all the existing right that's 
going to get us again we're going to do the same   exact thing we're going to run an advanced filter 
but this time we're using criteria and we want   those results coming in here so we're going to 
do pretty much the same thing as we did last time   just a few different things again we're going to 
focus also of course on our tax document database   i also want to make sure that b2 right i cannot 
load remember just to be just to build check right   we if we have no tax year it's going to create a 
problem right we need to have we need to make sure   that b2 contains a value we must have a selected 
tax year right so how do we do that so i want to   make sure that b2 contains a value just in case 
it doesn't just an insurance program so if if   income tax dot range b2 dot value equals 
empty then let the user know message box   please select a year exits up right nothing we 
can do unless we actually have a year exit sub   okay good so now we've got that now what we're 
going to do is we're going to focus pretty much   primarily on that document database so with tax 
doc database making sure that we have right again   what i want to do is i want to get that last row 
that last not that one again what i want to do is   i want to get that last row that last row is going 
to be based on column e right if the last row is   less than four then exit the sub right and then 
what i want to do is i want to run my advanced   filter so we're good to go as long as we have data 
so the advanced filter of course we're going to   use more data this time so we're going to what 
do we want to do i want to get all that document   information i want to bring that in so let's take 
a look at what results we want to focus on and get   that so inside our tax database of course we need 
the document id we need the document name and we   need the form type document id form type and the 
document name so we need to go all the way from   a through f i need to bring all that information 
here so original data is going to be right here   under a three right our row headers wrong 
column three through f that's what i want   where's our criteria our criteria is going to 
come directly from here that tax year is going   to be our criteria here o2 through o3 o2 through 
r3 that's what's going to be our criteria so   i'm going to update that from o2 through o3 
and where do we want those results going to   come from those results are going to come from 
q going to q through s q 2 through s and we want   unique v2 that's going to get us our results then 
we can focus on our results so the last results   row is going to be equal to of course going to 
be focused on q right q is going to be our last   let me use q is to get our last results row of 
those results so that's going to be column q   and of course if it's less than 3 then we can 
exit the sub out so so if the last results row   is less than 3 then x of the sub assuming that we 
have data then we can now move on i can bring that   information over so how do we do that well that's 
relatively simple so tax so let's do income tax   dot range focus on e5 e5 is the first row and the 
first column through g and the last result row   again plus two because our our starting row is on 
three and our database that value is equal to dot   range q2 q2 through s and the last results row and 
that's going to bring all the way over that okay   so q3 our first results are a 3 not 2.

Good i like 
that that's going to bring all the information   over okay so let's copy that now we remember this 
macro document load list automatically is already   set to run as soon as we make a selection change 
on year remember here's our selection change d5 if   document loads list this automatically sets a run 
as soon as we make a selection change on the year   so when we go back into income tax and we can save 
our work before running the macro and we select a   particular year now we have it okay so now here 
we see the roads we don't we know the rows there   but we don't need to see them so obvious so i'm 
just going to change the color of them make them   a little bit lighter generally you want to make 
them the same color as the background the same   color as the background but we'll do that we'll 
make them a little lighter but for your purposes   you want to make them the same color or you can 
use three semicolons in the conditional in the   formatting to hide them entirely so now we've got 
our document id now when i make a selection change   look we have the document row that's loading in 
everything is good so we have the document id   here as we load it in that document id is loading 
exactly the way we want to now all we need to do   is run the macro that's actually going to load the 
information right so now we know what document row   row 11 right so if we go into the tax document and 
we look in row 11 we know that we're going to load   all this information inside here and we're going 
to use data mapping to do just that we know the   tax year is j4 we know the transaction type is n4 
we know our tax returns the form other type is on   m6 so we know that those ranges so we also have 
some columns we know that in looking row 1 we know   the tax year is j4 we know the transaction 
types n4 we know the form types is m6 so   knowing that information here we can then bring 
in our data mapping very very easily onto that   so let's do that right now saving our work and 
we're going to run the macro that's actually   going to load that information in and that's going 
to be our next macro up so we're going to go into   the document macros here right here this is our 
sample i don't want to get you confused with that   and then so we're going to now load it i think we 
can close that sample out okay samples closed all   right so what i want to do is i want to bring all 
that information in so let's go inside back that   tax document and we have save update right so 
we've got to save update but let's add let's   skip that i'm going to add that load which is this 
one right here document load and then we're going   to go back out because that's the next one in line 
so loading that document is what we're going to do   is relatively easy document so let's add that in 
right now so the first thing is we're going to do   is i want to clear out a bunch of cells right i 
want to clear out all those cells so we're going   to focus primarily on that so with income tax i 
don't want to clear out a bunch of roads i just   copy and paste it's kind of a lot of typing so 
let me just copy that from some sample i got here   make it a little bit easier on me and you so you 
don't have to watch me type in all those ranges   so basically i want to clear out all those 
cells that makes it easier for both of us   then i don't cry all right so let's and now what 
i want to do is if there's a preview remember   we're going to have a document preview up in this 
space here right and so what i would like to do   is i want to have give it a specific name each and 
every time i'm going to call it document preview i   want to make sure that when we're loading it that 
preview of that document gets deleted it's always   going to have a very specific shape if it doesn't 
exist it could create an error so dot shapes   i'm going to call it doc preview give it a very 
simple name dot delete right i want to delete it   and then on air resume next on or go to 0.

And the 
reason is that if it doesn't exist it could create   an error so we want to route that also what i want 
to make sure is the when we're loading a document   the most important thing is that we have the 
database row that's associated with that document   that database row is located directly here 
inside b5 if that is empty for any reason   then we know we have an incorrect the reason is we 
could have a new document so we want to make sure   that b5 contains a value if not we need to let the 
user know so if dot range b5 dot value dot value   equals empty then let the user know message 
box please just let them know please   select a document and there's nothing 
we can do unless we have that exit sub   and now i have dimensions already some variables 
up here to help us long i've got document row   which is going to help us in document column 
we're going to need those things and of course   i have last row which we're using in result row 
so those are all up at the top so i want to get   that document row that document row is going to 
be exactly in b5 so i'm going to take that and   i'm going to put that up here so document row is 
equal to whatever that's our document row once i   have that document row then what we can do is 
we can then load it up okay but what i want to   do is just make things a little bit faster i'm 
going to turn off screen updating so application screen updating equals false and i want to make 
sure we don't exit the sub when we turn it on   before we do it so just copy that here and then 
make sure that we're turning it on before the   macro runs that's going to make things a little 
quicker equals true so inside here is we're going   to do the work so inside there what i want to do 
is i want to run of course a loop i'm going to   loop through all the columns in that so how do 
we do that we already have the id so that's in   column one so we've got column one covered here 
the document id so what i want to do is i want   to run it all the way to let's say our notes and 
what is our notes equals column nine so i want to   run a loop from column two to column nine and we 
have a variable called document column to do that   four document column equals two to nine and 
making sure to close our loop next doc column   okay and inside that loop what i want to do 
is i want to load the data that load dot range   where is it going to come from right so we know 
to make sure that we're going to load it up   we have that range located inside where it's 
located inside the first row tax document   database remember that range on row one cells row 
one and document column that's our range dot value   so this right here is our range this is m right 
here this is m4 j4 whatever that's our range   so dot value is going to equal what it's going 
to equal whatever is inside the row of that so   tax document database dot cells whatever is in 
that row not clear arrows never use that dot   cells and then what does that wrote that document 
row and what is the column that document column   whatever data is in there i want to bring over 
dot value okay so that's what we want that's all   we need to do it's very very simple to bring in 
all that data using data mapping if you haven't   seen it before then let's go so great i also what 
i want to do is i want to add in some additional   information so i want to make sure that the dock 
in preview now let's say i want to run a macro   which we're going to build up coming up next if 
we have a document path located in j8 i want to   make sure that that preview of that document is 
visible runs a macro so we're going to check if   j8 right does not equal empty then we're going to 
run the macro that is going to actually load that   document preview so we're going to check on that 
right now if dart range j8 does not equal empty   then run the macro what is that macro that's 
going to load that document it's right up here   document preview there's nothing in here now but 
we're going to be adding it up then run the macro   if there's a value run macro to preview document 
those documents can be pdf pictures or whatever   all right great so we've got that now so it's 
going to run and that's it that's all we have   to do on that sub so it's pretty much easy 
relatively easy to load that information in   okay great saving our work before running the 
macro then what we're going to do is we're just   going to load it up now of course we don't 
need the extra s in there that's certainly   not necessary and then we also need to add 
in the correct variable which is dot column   here continuing on okay it looks good all right 
so we've got all of our information here perfect   i like the way that that looks all the information 
now what i'd like to do is get that visible let's   say get that preview of that picture or whatever 
that document i want to place it directly inside p   so let's do that now let's write the macro because 
it's part of continuation that's going to preview   that document so continuing on we're going to go 
back up here we want to show document preview so   how are we going to do that well first of all 
one important thing is we need to know where it's   located it's going to be located in a specific 
folder that we've already outlined we've outlined   it here inside this dropbox folder i want to make 
sure that we're browsing for the correct folder i   want to make sure that that path is located here 
if not we can browse for it here i don't believe i   have a macro assigned to that but we'll browse for 
that we'll be adding the macro but you could just   easily paste of course the path in there but we'll 
add the macro okay so we have it so it's going to   be located here right in c3 and i've given it a 
named range called document folder document folder   that name range i'm going to copy that that's 
what we're going to use we want to make sure   that that's accurate right and when no no because 
if that's not accurate if we have no folder that's   located and of course we cannot load the document 
so that's the first thing we want to make sure   inside our document preview so we're going to 
focus on with the income tax with income tax   and also the first thing we also want to do is 
i want to delete any existing preview i'm going   to paste this here just for a moment and what i 
want to do is again just like we did before in   clearing that out i want to delete any preview 
i'm just finding it here where we added it in   don't remember so here it is right here so i want 
to copy this this here basically we're going to   do that right we when we were loading a brand new 
one in we want to delete any previous one right so   that's all that that's going to do is delete any 
previous document preview so we can add that in   okay so now what we're going to do is i want 
to make sure that we have the right document   folder that's what we're going to focus on first 
first of all let's put in the file path i've got   some strings already set up dimension document 
folder here file path here and file name here   we're going to be using those let's update that 
also we need texture and a few other things   so the first thing what i want to do is i want 
to set that file path that file path is going   to be located in j8 equals whatever is in dot 
range j8 that's the full file path that value   okay so that's it that's all we need here to make 
sure because if it's not correct then we're good   so if the file path is empty then we're going 
to exit this we're going to this is going to be   the document file path so if that's empty we can 
get rid of this for now we'll need it in a minute   if the file path is empty right then we not want 
to we know it's incorrect or perhaps if it's   incorrect if it's incorrect or if it's empty so 
how do we know that so first of all we can do if   let's do this file path equals empty then exit the 
sub just just in case just to make sure right exit   on no doc no doc no file path nothing to load up 
okay now what i want to do is i want to check to   see if it's accurate okay so here we can go this 
if the directory of the file path dot vb directory   equals empty that means there's something wrong 
or then what do we want to do then of course i   want to let the user know message box see if we 
can spell it right this time what is that message   box we want to let the user know please make 
sure to make let's say the document file path   is correct or browse for a new path okay then 
let the user know to browse so we're going to   write a macro right going to browse for document 
path that macro is going to be up here browse for   document file right i want to let the user know 
to browse first that's the macro we're going to   write in just a moment but we're going to give 
them the opportunity to do that once they do that   then they can get the right path so that's very 
easy then what i'm going to do is i'm going to   set that file path one more time right giving 
them the opportunity to browse for it copy it   and then if it's still empty setting is setting 
that variable one more time set variable again now   if it's incorrect or it's empty now we're going to 
do if i'm going to copy this one more time if it's   still empty or still incorrect then we're going to 
if then exit or we can do or right or it's empty   let's see it's complete or it's empty or file 
path equals empty then exit sub right giving   them a second chance but if not then exit the sub 
out exit on incorrect path very good so that's all   we need to do let's fix that up or if it then got 
to put the if in there that's kind of important   continuing on now that we've made sure that we do 
have a correct path and everything's looking good   what i want to do now is i want to make sure that 
our tax summary is visible right what i want to   do now is i'm going to just run a check right if 
they're clicking on this area here i want to make   sure that column ii is visible it might not be 
visible if we're showing this if we're showing   these columns it would not be visible so i 
want to just double check to make sure that   it's visible if not show it okay so we can 
do that with this check here so if dot range   i through i i'm going to check on that in column 
dot entire column dot hidden equals true if   it's hidden then i want to run a macro that's 
going to be called tax summary hide right so   inside here i want to show the tax summary so 
right tax summary i want to hide excuse me i   want to hide the summary i'm going to hide that 
summary hiding i want to run a macro it's called   hiding this summary we don't want that hidden and 
i want this visible right so if they click on here   but the tax summary is visible this section is not 
visible we need to make sure that this section is   visible by hiding the summary so we're going to 
run the macro called tax summary hide so that's   all we're going to do in here so i'm going to copy 
that here then hide so run macro to hide summary   and we'll call this show doc info all right so 
that's kind of important just to make sure all   right so once we have that what i'm going to do is 
i'm going to turn off screen updating application   making it faster just in case dot screen updating 
equals false and make sure to turn on it's   probably not necessary because we've already 
marked it uh false already so just in case and   then turning on to true but probably not necessary 
i think i'm going to remove it because we i think   it's not necessary because we've already done it 
on here right we've already turned it off on here   we're already turning it off and we're showing the 
preview right so before we set this to true we're   already running it there's no need to do it twice 
so it's probably not necessary good to go we can   get rid of that one line so what i want to do is 
i want to set that file tip i want to know what   the file type is that file type is very important 
if it's a picture we need to do something but if   it's a pdf if it's a word if it's excel we need 
to do something else so we need to determine   what the file type is so how are we going to do 
that well i'm going to use the last basically   three or four digits of that right that path is 
very important is it dot jpg if it's jpg or png   or gif then we know it's a picture otherwise 
it's some other document type so we're going   to set that file type we're going to use the right 
command and i'm going to use the file path of that   and i also want to determine the length i want to 
know the last digits the length of the file path   right i want to get the right part of it so the 
right length of the file path minus what i want   to do is i want to find that dot right where 
is that period but i don't want the first in   case that name contains periods i want to use the 
last one so how do we find the last one we can use   in string reverse reverse for that what that's 
going to do is going to find the last character   file path and what character am i looking for 
i'm looking for that period so it's going to   find the last one of that so what that's going 
to do is going to get us the extension get file   extension once we have that we can determine what 
document type it is let's add that quote in there   that's important so once we have that right we we 
can determine then what we'll want to do is based   on that file type we can use the select case 
so select select case is a great feature here   case and what type of cases that we're going to 
use based on that file type file type now if that   file type is a picture case is equal to right 
let's say png or maybe it is jpg let's see jpg   so these are for picture file types or maybe it's 
gif it's all gif or maybe it is a bm or jpeg right   we'll choose jpeg because on a picture type we 
want to do something else jpeg or otherwise maybe   it's a bmp right let's do i'm only focused on you 
could probably add a lot more than this but i'll   add some bmp and lastly we'll do jpg right with 
jp we did that jpg i think that's or tiff let's   do tiff ti ff okay good so if it's those types 
we know it's a picture type so let's just add   in a comment here called picture type in a picture 
type i want to do something else what do i want to   do i want to insert a picture so dot pictures dot 
insert and what am i inserting well i'm inserting   the file path file path dotnet i want to set a 
name to that so dot name is going to be equal to   doc preview so that's only if it's picture what 
if it's anything else case else if it's anything   else i want to do something else i want to insert 
an object right so here we're going to do dot ole   objects and i want to add an object okay what 
type of object do i want to add i want to add   based on the file name so that file name is going 
to be equal to the file path and i also want to   have the link is going to be equal to false right 
there's going to be no link false right and i want   to display as an icon and also false display as 
icon equal to false i want to give it a specific   name that name is going to be exactly equal to 
the one we have equals the document preview very   good i like that good so that's the only different 
thing we need to do everything else is going to be   the same setting the position setting the height 
regardless if it's a picture or an object right   an object can be a word could be excel could be 
pdf lots of things work well so now we can focus   directly on that shape regardless of what it is 
so we're going to do that with dot shapes because   we've given it the same name we can focus just on 
that doc preview and what do we want to do with   it well the first thing what i want to do is i 
want to lock the aspect ratio it's going to be   true that's really important because i want 
to make sure that it doesn't get contorted   okay so now what i want to do is i want to set 
some maximum widths and some maximum height so   if dot width because i don't want it too big 
is greater than let's just say 47470 then   dot width equals four seven zero it's just going 
to set the maximum width equals four seven zero   i also want to do the same thing for the height if 
dot height it's let's use something larger because   we have a larger 700 pixels vertically then dot 
height is equal to 700 let's call this set max   width and we're going to set the max height set 
max very good so now we set the both now i want   to set the left position the left position where 
we want to place let's take a look here i'm going   to place it somewhere right around p4 let's 
take a look right about here right about p4   is where i want to set it so let's set that left 
position based on p4 so the left is equal to and   again we need to call out the sheet in this case 
income taxes we're inside another with dot range   p for the left position of that dot left set left 
position now all we have to do is just set the   height set the top position dot top is going to be 
equal to again we can just copy this we've already   done it we're going to set the top position based 
on p4 that's it and paste it in there and set that   top position set top position okay very good 
that's end width we need to make sure that uh   everything else is set we're going to close those 
spaces up we are good to go on that i like that   and then all right i like that saving our work 
again always before preview now remember this   macro is set to automatically run if there's any 
issues we'll find out real quick and we'll take   a look all right we're good all right let's 
take a look oops put this in the wrong area   wrong this is in the wrong so we want this in 
preview there's one i want to drag it right here   preview right browse for document folder is 
something different so we want to put it in   the preview there we go all right let's take a 
look at this see if there's any issues all right   that's looking pretty pretty good it shows nice 
let's take a look at this shows that right where   we want it it's looking really nice i like that 
we gonna we need some more spacing here because   we're gonna bring that over so let's bring that 
over okay that's looking pretty good it shows   pictures it shows different things i like it 
it's looking really good okay continuing on what   else do we have want to make sure that we put 
everything in here making sure okay let's take   a look at that bug making sure all right let's 
change this to true for word documents we need   to update that there we go okay continuing on so 
this is a word document let's take a look at that   ah that looks nice okay word documents msoc 
true didn't work true works just fine for both   that's gonna be good all right looking very very 
lovely now we've got documents loaded we do need   to browse for the file we need to do two browse i 
need to browse for the specific file i also need   to browse for the specific folder here so let's 
add in those macros here okay so now we got the   macro in the document preview browse for document 
folder browser document file those are remaining   we've done everything else up to them we'll go and 
try to go in order now and we have save and update   but i want to make sure that it flows properly 
so folder and file are two good ones let's add   those in right now so for the document folder all 
we need to do is just set some information up so   we're going to do that right now so browsing 
for the folder once we do that oh we're going   to dimension the document right first of all i 
want to dimension this just so you can see how   it's done here document i want to make sure 
that makes document folder is different than   the named range remember just keep this in mind 
make sure that they're different because that   can create issues remember we have a specific 
name range called document folder i want to   make sure that we're using a different variable 
and not document folder so we're going to use   document and then we'll just use something called 
folder as file dialog so now what i want to do is   i want to set this document folder equal to so i'm 
going to copy that and we're going to set it what   type of it it's going to be a set document file 
and then it's going to be equal to application   dot file dialog and what type of file dialog 
do we want we want of course a folder picker   right in this case we're searching for 
a folder browse for the document folder   that's the one we want so that's going to be 
folder picker and now we're going to figure   some with with that specific one we created with 
the documents folder what do we want to do well   the first thing i want to do is give it a title so 
i'm going to title is going to be equal to browse   for document folder and then also what i want 
to do is i want to allow multi-select and that's   going to be equal to false right we only want them 
to select one folder and then if dot show right   does not equal negative 1 negative 1 is going to 
be meaning they've selected someone then go to no   selection and that's just basically going to 
drop it down below that so that there's no issues   no selection and then we're going to drop call 
this called no selection then go to no selection   okay so what if they have selected something 
inside the admin dot range i want to put it   right there and it's going to be we can put it in 
let's say c3 i believe it's going to go into c3   is the range it's going into so that's where 
we're going to place it c3 exit out of there c3   dot value equals dot selected items one so it's 
the single selected item and that's going to put   that full file path of that folder in there and 
that's it that's all we have to do now let's go   ahead and check for that and make sure that we 
are browsing for the correct documents folder   and this particular folder is where everything's 
going to go so i'm going to copy this and i'm   going to make sure that that's attached to this 
browse folder so i'm going to put it to the entire   button set signing that macro pasting that in 
clicking ok now when we browse for it we can see   that we've browsed for it okay that looks really 
good we're going to set that document folder up to   setting it up to here our documents folder 
where everything's going to go and clicking ok   all right i like that that's looking good now 
what i want to do is browse for the file the macro   that's going to actually browse for that file so 
once i browse for that file i want to copy that   and put it in the associated folder so what we 
want to do is give the user the opportunity to   browse for any specific file and then what 
we can do is we're going to copy that into   whatever year they've selected so we certainly 
want to make sure that they have a year so   dimension right the doc file as a file dialog okay 
now with the income tax income tax i want to make   sure that they have a year without having year you 
know we can't put it into any associated folder so   if all right let's take a look at what year 
right take a look inside i want to make sure   that j for contains a year right we cannot we 
want to put the documents into individual year   folders just as you saw without a year then we 
don't know where to put it so we want to make   sure that j4 contains a value or let the user 
know if dollar range j4 value equals empty then   message box let the user know please add a let's 
call it tax year before adding any documents that   way we know what folder to copy it into documents 
okay that's pretty good and then exiting the sub   without a year can't do anything else assuming 
that we have a year let's put that into a variable   tax year simply equal to whatever is inside j4 
and that's going to set up our tax year inside   a variable it's going to be a string variable it's 
a number but we're going to use it as a string tax   year obviously self evident continuing on 
what i want to do is i want to check for the   accurate document folder right if we don't have 
a correct folder or there's some issue with this   like when you get this file i want to make sure 
that let the user know hey you got to get a get   a proper document folder before we can continue 
forward so we're going to check that for now so we   can do that the first thing let's put this comment 
check for accurate document folder so we're going   to set that into a variable called the doc folder 
is going to be equal to admin dot range and then   also we have a named range which we can use here 
right document folder that's our named range that   we've already set dot value and i also would like 
to add the backslash onto that so so we're going   to use the and then we'll set the backslash 
on to that so that we can do that backslash   onto that okay because we're going to need that 
and it's going to be called that document folder   obviously continuing on what i want to do is i 
want to make sure the accuracy of that so let's   do this if the directory checking that doc folder 
dot vb directory equals empty or we can check the   admin now admin dot range this one document 
folder if that's empty as well document folder   dot value equals empty then let these know 
you can use empty or double quotes they will   both work just fine then let's use a no 
message box please set a document folder   okay so we have one right we're browsing for 
the document folder so we can do that right up   here document folder we can just prompt the user 
to browse for the document folder at that point   right so we can continue on so letting them run 
the macro run macro to browse for doc folder okay   good now i'm going to run one more test right if 
it's still empty then we're going to exit out of   the sub so i'm going to copy this after that point 
and then we're going to run if if right either   one of these conditions are empty then exit sub 
right giving them that second chance but if then   still empty then continuing on okay so assuming 
that they've passed all those texts now what i'd   like to do is i want to set the document file 
right this one document file so set document   file and it's going to be equal to the application 
dot file dialog right and this one's going to be a   file picker so this one we're going to look for 
file picker i want to do a specific file picker   so now with the document file we're going to do 
something we want to do a few things first thing   what i want to do is give it a title right so 
the title is going to be equal to let the user   know browse for document file so now that they 
have the a proper title that they can go on to   i want to add additional information right i'm 
going to let them know they can select any type   of file anytime there's no filter onto it so 
we're just going to add this kind of filter   filters dot add in this case right i 
just want to put in select file select   file and then a comma and then what kind of 
file can they select any type of a file so   i'm going to use the asterisk here and which 
is the wild card and then i'm going to use the   period and then we're going to use the asterisk 
one more time meaning it can start with anything   and end with anything and then i'll just do one 
right so that's the position one is the position   and then allow multi-select in this case no equals 
false right they're not allowed to multi-select   and then again we'll make sure that 
they've selected something if dot   show does not equal negative one would be 
meaning they have selected something negative one   then go to no selection just as we did above right 
next look and then going no creating an outfit no   selection and then adding a colon onto there okay 
but if they have selected something what do i want   to do well i want to do a few things right 
i want to set that file path into a variable   and i also want to set the file name so the 
file path is equal to dot selection select   item 1 that means the full file path full file 
path i also want to set the file name what is   the name of that file it's going to be equal to 
the directory of that file path equal directory   of that this file path so we're going to set 
this is the file name here okay that is the file   name so we've got both because i want to set 
why is that important because i want to set the   default file name i want to place that directly 
in here and then they can change it to con and   the full file path is going to go here but what i 
want to do from wherever its current location is   i want to copy that into the proper folder right 
now no selection if they haven't selected anything   we can't continue so we can exit out of that so 
everything's going to be written down in here   okay so the first thing what i want to do 
is we've checked that i want to make sure   to check to see about that file pal so let's 
update that file path it's actually going to   be more than that that file path is not just 
that right it's more than that if i want to set   a different file path so let's go ahead and take a 
look at that i know that sounded kind of confusing   it was for me too if the file path so here's what 
i want to check now you're browsing you're pulling   it from a folder but what if you're pulling it 
from the same folder that you actually want to   put it in if it's already in the right place then 
we don't need then we don't need to copy it over   so i want to check for that so let's do that if 
the file path is equal to right dot selected items   one that means the current file path then go to 
let's just put in correct folder it's already in   the correct folder so we can skip all that 
in correct we can skip copy it over folder   okay so we don't need to copy that if it's 
already in the place we wanted to put it   okay so that's good enough so we're good so let's 
go ahead and set that new file path i want to   put that new file path and i want to put it in 
something else but before we do that i want to   update the file path i'm going to be not only do 
i want it on that i want to select it up so let's   switch these i'm going to put the file name first 
and then what i'm going to do is i'm going to   update that file path based on the text year so 
that file path is going to equal to the document   folder okay and i want to put the tax year in 
the tax year right because i want to put each   individual has to have that tax here and i want 
to put in the backslash onto that so there that's   going to be that full file path so that let's do 
that backslash here right and i want to make sure   that we're putting in that file name right and the 
file name that'll be it okay so that's that's our   full file path of our destination let's put that 
in here let's add that in destination file path   destination file path okay so it's our document 
folder our tax year and our file name that's where   we want it to go to right we've already added the 
backslash onto this up here right we've already   added that up here so we don't need that error 
so the document equals the texture and the file   name and don't forget the and file name okay so 
we've got our file path set already now what we   want to do is we already now here we've got our 
new file path equal to our existing file path   if they're the same that i like that better okay 
so that looks correct now what i want to do is i   want to make sure that it to see does do we have 
a folder for this tax year or not right if not i   need to create that folder just to make sure so we 
can do that with this if the directory of the doc   folder and the tax year vb director i just 
want to see if that folder exists or not   equals empty that means that that means that 
that tax year folder doesn't exist there's no   folder right in this case let's take a look at 
this here we have our tax manager our documents   here there's no i want a individual 2018.

I want 
those folders i want those i need to know does   it exist or not so we can do that if it doesn't 
equaling empty then we can make the directory then   make the directory and what directory do 
we want to make simply this right here   which is the document folder and the document 
that's going to add that year folder onto that   so there we go so what that's going to do is going 
to create folder if needed for the year all right   good so we've created the folder just to make sure 
that that exists and now what we can do is we want   to make sure that we are know does the file exist 
in the folder or not if we're copying a file from   one location to another location it's going to 
create an issue if that file exists already in   the folder so we need to check that so let's do 
that right now if the directory file path dot vb   directory does not equal empty then we need 
to remove it maybe we're making an update then   kill the file path so that's going to delete if 
exists now here's the important thing this is   why we're skipping this right if that file name 
if we're pulling it from the same file that's   why we're skipping it's already in the correct 
folder so we're skipping this delete so that's   very important distinction now if it is not now 
after we kill it after we delete it if it exists   now we're ready to copy the file from wherever 
the user has browsed for it into the new folder   so we're ready for that so we can just file copy 
for that file copy and then we're going to just   all we need is the source now that sources course 
is the selected items one that's the source and   our destination is simply file path very simply 
and that's going to simply copy the file to the   new path okay good so we've taken into every 
consideration all right now that we have that   all we need to do is update j8 with the full file 
path right j8 is going to can take on that so   income tax and then actually we can do income tax 
here dot range j8 dot value equals again file path   okay set new file path i also want to do k10 i 
also again as i mentioned before i also want to   put that file name the default file name located 
right here in k10 so we're going to do that right   now so i'm just going to copy this and then 
update this to the specific range which is k10   k10 and that's going to take on that variable 
that we've already added called the file name file   name is going to be that okay we can also the 
directory of the file path we set it up now we got   the file name file name good i like that set file 
name all right now we have that so what i want to   do now is that we've browsed for that what i want 
to do is run the macro automatically so document   preview right once we've added that file that's 
why i had that let's see if it's slow application   screen updating turning it false and off and on 
but i think it's going to be fine so now what i   want to do is i want to preview that document 
run macro to preview document good and then   if there's no selection of course they haven't 
selected anything yet end with and there we go   and sub now we can add that end with up there so 
we need that that's sufficient up there all right   no selection and within sub i think that's perfect 
this is the width for that specific document file   this is the width for that sheet income tax 
sheet probably don't need to so as long as   so if you see how it says income tax here if i 
put that down here before the end with right if   i bring that down here won't matter too much 
right between the end with right we're done   with selection but keep in mind no selection has 
to be after right if they've made no selection   we need to add that after and put that right here 
meaning no selection all right good so let's take   a look at that so now we're within the income tax 
sheet so i can remove this here just in case it's   not necessary to have the sheet there because 
we're inside the width we're no longer in the   width of the specific document file here all 
right we've added that up all right so let's   take a look at that saving our work before we run 
that macro now what we're going to do is we're   going to browse for that macro it is this macro 
browse for file dialog that i'm going to attach   to that button that we have created right here and 
we grip it so i want to add hold down the control   on both the icon and the button right clicking 
it adding that macro pasting that macro in here   and then we'll check for any issues and fix 
them accordingly browsing for that macro now   we can browse for a file we can use any file i 
can use even use an existing one which is fine   and we have a form here a png form clicking ok 
all right and it browse for that it looked like   it found it good and and it put the file name 
here let's add one more all right we're going   to add one more here just to make sure that we can 
change it browsing for it here clicking ok making   sure that it changes all right that looks really 
good it's changed it it's added that in here   it's put in that new file inside the year right 
giving it a specific name putting that name here   all right we are looking really good of course we 
can add an amount onto that right if we want to   now what i'd like to do is now we've added all the 
information now we've previewed it accordingly but   we need to save this document right i need to make 
sure that that is either saved or updated right if   it's going to be saved if it's if it's saved it's 
a new one how do we know if it's a new document   or not well we can look right here the document 
row we know if the document id is gone from this   we know that document row is going to be empty we 
know it's a new one however it's an existing one   we know there's a row associated so it is 
the macro that's going to allow us to save it   and that's the macro that we're going to go 
all the way up here save and update right here   that's the macro that i want to write now with 
you so we can show that how we actually save   and update that okay so the first thing what 
we want to do is i want to make sure that we   have the required field so with income tax 
i'm going to check on those required fields   one of course is a year and we also want to 
make sure that we have a type those are really   important so i'm going to look for j4 and 
n4 we need to make sure that we have a year   we need to make sure that we have a type so 
both of those are required so if dot range j4   value does not equal equals empty or dot range 
and four equals empty in either one of those   cases let the user note equals empty then let the 
user know with the message box that those fields   are required okay we'll just call it tax year and 
we have the other one of course uh let's see what   are we calling that transaction type are required 
exiting the sub we can't move on unless we have   this okay continuing on now that we know that we 
have those we can set that tax here that tax year   is already variable we created and that's going to 
be based on whatever's in j4 so we can copy that   and then make sure we put equals and paid that's 
the tax year once i've got the tax i also want the   document name what is that document name it's 
going to be located inside k10 remember k10   is going to take on our document name document 
name so good now that we have our document name   attached here now what i want to do is i want to 
determine is it a new document or not remember   we just discussed it i need to know that and it's 
going to be based directly on what is located in   b5 if b5 is empty we know it's a new one so if dot 
range b5 dot value equals empty then new document   and then end if else existing document so what 
are we going to do if it's a new document if   it's a new document i want to do a few things i'm 
going to set that document row it's going to be   the first available row right so we need to set 
that document row it's going to be equal based on   the database the tax document database dot range a 
99999 and xlr dot row plus one the first available   row first available row i also want to set that up 
make sure that next document id now we've already   know that next document date we're using the max 
formula so i'm going to take that next document id   i'm going to place it directly in b4 and i'm 
also going to place it in the first available   column right here and this b32 and column a so 
i'm going to do those two things with the next   document id so the first thing running dot range 
b4 is going to be equal to whatever is located in   side b6 is where it set the next available id so 
dot range b6 okay so that's going to take on there   6 is going to be our next dot value next id okay 
also i also want to take it and place it in in   the column right so i want to put it directly 
inside here a and that brand new row the tags   document right here so a and the document 
row dot value is going to equal whatever's   also in side b4 that we just placed in there 
okay so we can place that right in there that's   also going to place that next id that's great so 
we've also placed it and also what i want to do is   i want to place a document row we may need that 
in the future right that's kind of important so   i want to place the formula here in row so that 
could be important so i'm going to place that   formula directly inside here okay so let's that's 
going to go in column j so column date tax doc   database dot range i don't think we're using 
it in this training but we might in the future   doc row dot value equals that formula 
right that formula is right there   set row formula okay that's all we need to do if 
it's a brand new one but if it's an existing all   we need to do is set the document row and that's 
going to be based directly on whatever's in b5 so   that document row is equal to whatever is in b5 
document row so now that we have that now what we   can do is regardless if it is a new or an existing 
we're going to run a loop using data mapping for   the data let's call it doc column just as we did 
more loading it equals two to nine two two nine   and then we're going to close our loop next doc 
column i'll move that up okay so we have our loop   here and inside that loop we're going to basically 
do the reverse right if we remember we look down   on here on the loading dock let's take a look in 
here the loading we did something very similar so   i'm going to copy that and just do the reverse 
of that so i'm going to copy that i'm going to   bring it right up here into the save right here 
and that's where we're going to place it right   here so i'm going to paste that in here but now 
we're doing the actual opposite we're taking   whatever is in the range and we're placing it 
inside the database so moving our equals over here   so now whatever's in inside this range here we're 
placing it inside the database so that's going to   save data so basically the opposite very very 
easy to program okay good so we've got that   we've placed all the database now what i want to 
do is i want to once i have updated this right i   want to make sure let's say we added a brand new 
let's say we added a brand new year right let's   say we we put in 2023 right that year doesn't 
look so i want to run the macro that's going to   automatically load these tax years again but i 
want to set that into a variable because i want   to know what year was selected and i want to make 
sure that that year's continue to be selected so   the best way to do that is simply to put those in 
two variables okay so we've already got the tax   year and the doctor name into variables here 
that's going to really help us going forward   okay so how do we do that so the best thing to 
do now is set the tax year so first i'm going   to run the macro that's going to load in those 
tax shares right i want to do that right in here   let's load that macro where did it go tax years 
here top list so i'm going to run the macro   and then i'm going to place that drawn under 
save so so we're going to let's say refresh   year list and now what i want to do is i want 
to i want to set the row right if i've just   refreshed the list right if i've refreshed that 
list we don't know what row we don't know what   row selected i want to look for the year that 
tax year if it's found on on let's say 7 i want   to take that 7 i want to place it directly 
inside b2 if it's found so here's how we're   going to do that first thing what we're going 
to do is we're going to go to on air resume next   and then what we're going to do is i'm going 
to set something just like that dot range   b2 is what i want to set b2 dot value is equal to 
what the row of which it's found on i'm going to   look for that row how do i know where it is so 
we can do this dotted range d5 that's the year   list d5 through d let's just use a larger own 
nine nine nine dot what i want to look for i   want to find it i want to find that tax here find 
what am i looking for i'm looking for the tax year   i'm looking to excel values and excel whole and i 
want to return the row i want to know the set year   row and i want to place whatever year it's found i 
want to place that directly into b2 i also want to   do the same thing for the documents right i want 
to set the same thing and then we can do on just   in case it's not found it could re there so that's 
why we're wrapping it on our resume next in on our   go to zero okay great so now that we have that i 
want to make sure that it's not empty so if doll   range b2 is not equal empty b2 dot value does not 
equal empty then what i want to do is i'm going   to set that document make sure that we've selected 
the right document i'll look for that document and   find it dot range here it's not equal empty then 
it's called on accurate selected you're accurate   selected yeah it's gonna be a long training and 
if they're all long this one's gonna be longer   than normal so let's take a look at that so now 
what i wanna do is i wanna refresh the document   list right we just created a brand new document 
or saved it maybe we updated the name maybe we   don't know what we did but what i want to do is 
i'm going to find it in this list and i want to   make sure that it's the correct selected row so 
what i want to do is i'm going to refresh this   list if i've just changed the name this list must 
be refreshed right so we're going to run the macro   that refreshed that list right the document thrust 
list it is this documents list load so i want to   run this macro to refresh the list of documents 
now okay so we're going to run the macro run macro   to refresh documents okay good so now but now 
we also need to set what row right if i've just   deleted that we don't know what row that row also 
needs to be set in b3 right set that selected row   so again we can do that just like we did before on 
a resume next and on air go to zero right wrapping   that in in case it's not found i'm going to look 
for it i'm going to place that row in b3 dot range   b3 dot value equals what i'm going to look 
for that it's going to look for in a specific   range so what range is that dot range here f 
it's going to be looked at document name i'm   looking for that in column f5 through f in the 
larger or 999 dot find what am i looking for   i'm looking for that document name document name 
and i'm looking at excel values and excel whole   good so i like that and i want to extract the 
row from that set selected document row okay   very good i like that and now what we're going to 
do is we've saved the document i'm going to run   this little macro is going to do a saved message 
we need a shape called document save message i   forgot to create that but it's no problem let's do 
it right now i want a little save message that's   going to go up here so i think all we'll do is 
just duplicate i'm gonna copy this shape paste it   here not the text and copy this shape here paste 
it here that way it's got the same size and we're   gonna call this document save document saved we're 
going to send to that and give it a specific color   here center that and then give it a little bit 
different uh probably 1.4 okay and then i'll give   it a nice color shape fill something different 
right let's do this here and then give it the   uh let's a different font color okay good i like 
that let's do that and then do this here giving   it that text perfect okay i like that okay so now 
we've got document saved so again i'm going to go   back and copy this to make sure i've got copying 
the name of this message going back in here i'm   going to assign it that name okay now what i 
want to do is when the document's saved i want   this message to fade out we've given it a name we 
know it's document saved it's purposely giving it   a different color i like that so this is basically 
going to fade out this we've been over this before   but i want to run this macro when it's saved 
i'm going to copy this and paste it down here   run fade out message macro very good as long as my 
voice is still with us all right i'm here for you   guys okay so it is that macro that we just created 
that we are going to assign to that button so   all i need to do is just copy this check for any 
errors of course we save before we run the macros   assign that to both the button holding down the 
control both the button and the icon assigning   that to the macro down here assigning that 
don't need that part no not that part just   the macro itself good i like that saving our work 
before running and then we're going to click save   okay and we're going to check it out see 
now our document save message is fading out   that looks good okay so it faded out it saved 
everything very good and let's make sure it's   saved let's just do this test notes here saving 
the document here we need to have some borders   around there going back out of it back into it to 
make sure it's saved it did i don't know i think   we need a border around here don't we look better 
with a little bit of a border i think and then we   can put that border around it not there there and 
there okay it looks good to me like it all right   looking really good it's saving properly i like it 
we have to add this new document delete document   view tag summary all right let's do that those are 
relatively easy macros let's get those written up   now and we'll go down here we've almost done with 
this little module here document new relatively   easy so let's get that done again all i'm going 
to do is i'm just going to copy that range here   we're going to work with our specific thing all 
i need to do is we've already done it here i'm   going to add one thing here so with income tax 
okay i'm going to paste everything out i want   to add one more thing to this clear content range 
and that means i want to add whatever document id   is located in b4 i want to clear that out too 
so b4 i also want to make sure that of course   we are deleting any type of preview that might be 
here so i'm going to copy that and paste that up   directly in here so that's going to be for the new 
one okay so what else do we have on the new it's   relatively simple maybe all we want to do is just 
prompt the user to select for year so dot range   j4 i'm going to have them select that's the year 
select okay i like that that is the macro that's   all we have to do we're going to assign that 
macro here to that new button there okay moving in   and then this document here so holding down 
the control and then what we're going to do   is assigning that macro way up here we don't 
need it there pasting that in clicking ok so   again always saving our work especially when 
we use clear contents right saving your work   before running a macro clicking new everything 
looks good everything got cleared out let's add   something we need to add a tax here notice we have 
that i've got a named range formulas name manager   right we've got something called tax years here 
that's the one that's going to be a named range on   our tax years that's sufficient we can add that to 
a drop down list so data validation right adding   that data validation right here and we're going 
to use a list and we'll call that tax years okay   equals don't forget the equals equals tax years 
okay i like that now we have a drop down list of   tax years we can add to it we have a transaction 
type let's do an expense we have a 1099e we can   browse for a particular document anything is going 
to work for us at this point giving it a name it   displays properly fretters and giving it a mount 
okay i like that let's contest notes making sure   that we're adding that okay saving that document 
it assigned it that row the id the row looking in   our database all the way down here everything 
got added in the save is working quite nicely   that's looking good and make sure that it got 
added in the right folder let's look in here   what folder was that we just added in 2021 folder 
so let's take a look inside the folder that got   added in accordingly taking a look two zero two 
on 1099 form period this dot i got it in the right   folder okay that's looking really really good 
all right new document now let's go to the macro   that's going to delete the document this one's 
very very easy so let's scroll down here deleting   the document very very easy so what do we want to 
do first i want to give the user a prompt to make   sure that they wanted to do that so we can do that 
message i think i got that on auto hotkey to make   it faster there we go are you sure you want to 
delete this not appointment document that's auto   hotkey it's a free software you can download 
it that makes things a lot quicker otherwise   it'll take six hours instead of three so let's see 
continuing on now that we know that they've wanted   to delete it what we want to do is with income 
tax focus on that sheet if i need to determine   has it been saved or not right maybe they want 
to delete it and has them and save which is fine   so how do we know if it's been saved of course we 
always use b5 it's going to let us know if b5 is   empty we know it hasn't been saved if dot range 
b5 dot value equals empty then we need to skip go   to not saved and that's going to drop it down 
here not saved and then all we need to do is   just simply run the macro to add new beyond that 
so that's going to be relatively easy so what if   it has been saved if it hasn't saved i need to 
extract that row document row is going to be   equal to whatever's in v5 extracting that inside 
a variable okay document row because this is that   row that we're going to have to delete inside our 
document database so we can do that with the tax   document database dot range document row and okay 
adding that colon there and the document row dot   entire row not column row dot delete okay that's 
what we always saved before just in case deleting   that row okay so we're going to delete the row and 
then that's it that's all we have to do then what   i want to do is simply run the macro that's going 
to add the row right delete let's put this delete   database row okay so now that we have that so now 
all we need to do to clear out the contents of   this is simply run this macro here now we're going 
to run this macro regardless if it's been saved or   not so we're going to do here run macro to add 
new okay so that's going to be all we need to do   is pretty much easy okay good enough okay then 
there we go all right good so i like that and   then we can save that work always save and then 
assign that document delete macro to the button   that we previously created so going in here delete 
document here selecting here and here right click   and then assign the macro pasting that in there 
clicking ok ok so that one we just created here   delete the document are you sure you want to 
delete the document no good that works fine   delete the document again are you sure you want 
to delete it yes okay it's going to delete the   document actually what we need to do is refresh 
this list right we really need to refresh that   list let's do that now i like that so one more 
thing to do and that's refresh that document   list kind of important right so let's go up here 
scrolling up here and look for that macro right   here document list here that's the macro that 
i want to run on delete kind of important there   so going down here right and then document 
delete here let's call this that's a reload   document list okay good let's try it one more 
time make sure that that list gets updated here   delete the document yes let's put in yes and good 
now we've got the list refreshed i like it perfect   that way we don't have any issues all right it's 
looking really good i like that now what i want   to do is i want to be able to what if we want to 
print that document all right well we're going to   print the summary that's going to be it for all 
the documents we don't need anything else on here   that's sufficient enough all right document delete 
so we've covered so far we're almost done we've   covered save document delete view tax summary 
now we're going to focus on the tax summary   so what do i want to do when view summaries 
click i want to hide everything we have here   and i want to show this tax information all 
right so let's continue inside the module for tax   summary it is these modules that we're going to 
go into now so we've got show and hide so that's   what we're going to work on here so what do we 
want to do when i show it well when i show it i   want to show a few things okay so we're going to 
focus again on with income tax inside that sheet   what i want to do is i want to make sure that 
we're deleting any document preview we don't need   to show the document preview so if there doesn't 
exist of course it would do dot shapes doc preview   right when i'm showing the summary we can delete 
the preview of the document dot delete okay delete   doc preview we can always recreate it just by 
selecting it again so it's no problem preview   okay so and then on air resume next okay so now 
that we have that what i want to do is i want   to show and hide certain shapes accordingly 
right so when i want to show that tax summary   right we want to show the tax summary what do 
we want to do i want to hide this group this   group here should be hidden because this is all 
associated with the documents so it's called the   document group that's the one i want to hide 
so that's what we're going to do first dot   shapes this one here document group dot visible 
equals mso false right hide the document group   hide document group but i also want to show the 
summary of europe so dot shapes and what is that   summary group what is the name that we assigned 
to that right if we scroll over here we've got   three different buttons here and we've got let's 
uh reset that so it's going to show that here   and didn't we sign it and let's call that make 
sure that we assign that a name for that i think i   may not have gotten to that there it is tax 
summary group that's the name we assorted   with those three buttons that's the one we want to 
show visible so it is that one summary dot visible   equalism is so true so show summary group so 
once i want to show i also want to basically show   there's going to be a chart that's put in there 
i want to make sure that we add a chart we'll add   in that later so i'm going to show and hide that 
chart we'll be creating that soon give that a name   but also what i want to do is i want to hide 
certain columns which columns do i want to   hide well i'm going to hide let's say i through 
z right all these columns here associated here   let's say i through z i want to hide those 
columns so dot range and then i through z   entire column dot hidden entire column dot 
hidden equals true right want to hide all   of those columns okay so now we got the entire 
columns hidden true and now what i want to do is   i want to show the other columns which columns 
do i want to actually show the summary columns   those summary columns are a a through a e i want 
to show these columns okay so we can do dot range   and then it's called a a through a e those 
are the columns so i want to hit it dot   entire column dot hidden equals false okay so we 
want to show those right by and that's it that's   all we have to do okay so the rest is basically 
just the opposite so i'm going to copy this here   and then just switch them around for hiding right 
when we when i want to hide that summary i want to   do exactly the opposite right this is probably 
not necessary here we don't need that part here   document group of course we're going to show 
that right so i'm going to do msoc true and then   comment show the document group then this is going 
to be false right we want to hide the summary   false hide the summary group then i'm going to 
switch these true and false so this is going to   go to false and this is going to go to true then 
all we need to do is assign those buttons to the   buttons that we've created i like that that's 
look really good so the show one tax summary   show that's going to go on our first button that's 
located right here on the left side here view   tax summary so i'm holding down the control right 
click clicking n that's going to paste that in and   then tax summary show that's the macro that i want 
to see again saving our work for running macros   view tax summary good that's why we don't have any 
data in here text but in a hide summary i want of   course i want to do that of course and we can also 
search for that macro here tax summary hide is the   one we want to do there and then hiding the tax 
summary okay i like that there you see all we need   to do again is select that to show it perfect okay 
that looks really good so now what we want to do   is i want to run a macro that's going to actually 
refresh the data that's a very easy macro of   course it's going to be based on the selected year 
i want all those transactions for the selected   year to appear here okay and that happens to be 
the next macro that we're going to write called   tax summary refresh and the first thing 
what i want to do is i want to clear out   any data that might be in here so from aa9 through 
ae i want to clear all that data on so income   let's focus on that income tax dot range aa9 
through a through a e and a large row student999   and then dot clear contents right we want to 
clear all the clear any existing transactions okay   so we've cleared all that out now we're going 
to really focus on our tax document with   tax doc database right because that's where we're 
going to run our advanced filter to get that so   again first of all we're going to get the last row 
i need to know the last row data just as we did   before if the last row is less than then of course 
four then we need to get out so we use four right   nothing else we can do if there's no data assuming 
that there is data we can run our advanced filter   just as we always this advanced filter is going 
to go all the way starting in row three right our   data starts on row three and it goes all the way 
to column j so j in the last row okay we want to   run our our criteria now our criteria is going to 
be based on that year we already went over that   year here we know that that year is our criteria 
here our tax criteria is right here based on that   indirect formula so our criteria is going to be 
o2 through o3 and where do we want the results   come i want the results to come here some results 
that i've already set up here so o2 through o3   and then our result is going to come from u2 
through z2 okay so let's set up that up here   o2 through o3 and then the results of course are 
in u2 through z2 u2 through z2 now we need to   determine the last row the results so the last 
results row of course is going to be equal to   range of course not going to base it on column u 
not a so the last results row if the last results   are less than three then there's no data so 
last resorts are less than three then exit   the sub assuming that there is then all we 
need to do is bring over that data so income   tax dollar range aa9 through ae and 
the last result row now of course our   our data that we're destinations is going to 
start on row nine our original data is going   to start in row three so we need to compensate for 
that by adding six dot value is equal to dot range   u three that's our first row of the results all 
the way through y remember going to bring y why   is that because i actually i don't want to bring 
the rows what i was thinking is that we might need   the path we really don't need the path so i just 
going to bring it over to y y is the last amount   that's sufficient enough we really didn't need to 
bring the document path over but if we needed to   loop through the documents for any reason we could 
so amount y is going to be the last column that's   sufficient so y and the last results row and the 
last result row good so that's going to bring over   all the data over and let's of course saving our 
work and when do we want this to run well i want   this to run in a few different times right i want 
to want this to run automatically when we're going   to show the tax summary when we show that tax 
summary i want this to run automatically so run   macro to refresh data so every time we show that 
it's going to refresh that data that's going to be   nice that way we have it all the way over so let's 
take a look at that and then see how that's going   to work and so we're going to hide the summary 
we're going to view the tax summary check out   let's fix that bug aaa clear the contents one too 
many zeros probably in there and then continuing   on yep we forgot a row that's kind of important 
right so there we go very important actually   continuing on okay and that's why we save our 
work all right okay so let's take a look we   got our data in here that's looking good but now 
what i want to do is i want to have some i want   to know the total income the total credits so how 
do we do well that's a relatively sum if so equals   sum if right and what are we going to sum it on 
i want to sum the range right what is our range   here is going to be our transaction type 
right so i'm going to go to a large row   let's do 999 and it's going to be based on income 
right so i want to know only those that are income   and where's that sum range it's going to be this 
amount here so using the same number of rows right   we want to go to that and it's going to be 999 
good so we've got the income that's going to be   the sum if so we notice that we've got 45 000 now 
what we want to do is do the same thing for credit   so i can just simply copy that bring it down 
here and change it to expenses so instead of   income i only want to focus other is not going 
to be affected so now expenses okay expense all   right so now we've got our expense and of course 
our total tax billing is simply our income minus   our expenses and credits okay and now we have our 
total taxable income perfect okay i like that all   right that looks really good now let's go ahead 
and put in a chart we'll print the summary zip and   email very easy okay so let's get a chart in for 
that and let's go ahead and add a chart into that   this is the data that i really want to focus 
on so what i'm going to do is i'm going to do   insert right and we can do recommended charts 
see what they recommend chart title this looks   pretty good i like that so we can select this and 
click ok we do need to do a few things with it of   course i don't want a chart title and i don't want 
these this rows here and we don't want these and   um i'll put labels so we don't need that here and 
uh good let's take a look at that shrink this down   a little bit and we're going to update the data on 
it so we're going to add some additional filters   and some additional information i like the colors 
but let's set a color there and let's scroll this   up i'm going to set it set a color there i like 
this color here let's go down with this color here   that's good now i want to add some additional 
information from that i want to add that we got   the series okay but let's format the data in 
here i want to format the plot area just fix   it up a little bit so we can look at it and make 
it look nice so let's add in let's take a look at   not the plot area but the chart area i want to 
add in some additional information here not not   so much and inside the data series so i'm going to 
select on the data series and i want to basically   reduce that width right because we don't have a 
lot of space so i think that's to be sufficient   and that i'm going to bring it down here right 
we only have a little bit of space to work with   and we have very very limited so i'm going to 
bring it down here and like right about here okay   let's uh let's add a little bit more i want to 
make it exactly the same and then we'll add some   data labels on to that and then reduce that okay 
that's looking good let's get rid of additional   spacing here we don't need it and then we'll 
also update that accordingly so we don't need the   background here so we can no shape fill on that 
no fill on that and then i'm going to update this   i'm going to add and let's drop this down we'll be 
using it a little bit more for this customization   and then going into i'm going to add this give 
it our green font bold and italic for that   and we want to add some data labels onto it 
so i'm going to add data labels onto that   and then those labels we're going to get those 
visible in just a second making also them the   same color bolt italicized bringing those down 
we don't need the extra spacing that's looking   pretty good i'm going to move that and size 
it up accordingly there and then we also don't   need the extra there okay that's looking pretty 
good all right we'll just size it up bring it   a little bit bigger than we need to to make sure 
we compass all the data ooh that looks quite nice   all right good we've got a nice little chart 
there and now i want to give this chart a title   i want to make sure that we may not need to hide 
it let's hide the summary and see if it looks okay   that looks all right i like that it looks pretty 
good and of course it could be it's good enough   for our purposes so we've got a nice little chart 
title there we're good enough to go on that let's   print the summary and zip an email write those 
two macros and then i'm going to let you go   okay so the last things that we have to do 
print that summary that's very very easy all   we need to do is simply set our print range and 
then all we need to do is just print it out so   let's write that macro right now inside that 
so we're going to set i've got the print range   all the way up here as a string so we're good to 
go on that and so we're going to set that print   range is going to be equal to we need to set it 
to the equals to the income taxes we don't know   we need to set the last row.range and then we'll 
do we'll set the title aa4 that's our first row   that we want to print through a e and we just need 
to get the last row and where's that last row and   income tax dot range a let's use aa999 i want to 
know the last row of the data dot end xlr dot row   okay dot address okay so i want the address 
of that that's gonna set print address   into a string into string okay so once i have 
it into a string i can then use the page setup   so income tax dot page setup and then what are we 
looking at i want to look at of course the print   area is going to be equal to whatever's in the 
print range so print range okay so now that we   have that that's going to actually set the print 
area set print area once we have that we can just   print it out income tax dot rank dot print dot 
print print out and what do we want to print out   well i want to print out obviously from two all 
the copies preview we don't need to preview it so   we can do that active printer true we want to set 
the active printer print to file now false let's   do false on that we don't need to print a file 
collate print a file name no ignore print areas   and that's false we don't want to do that okay 
so i like that that's going to do that now all we   need to do of course is assign this macro to the 
button that we've created so print summary is here   and i'm going to hold down the control on those 
two buttons let's make sure i've got those holding   down the control here and here assigning the macro 
here pasting that macro and clicking ok all right   so printing that macro here it's going to print 
it to the default printer mine's snagit so let's   minimize that you can see that let's do that 
bringing that down here all right that looks   good it looks nice everything's printed the tax 
summary for your ending we need to add that year   ending that reminds me thanks for reminding so we 
need to do that that year ending is important we   need i've got a border around here i don't want 
that so right format right shape outline and then   no outline on that okay there we go and i don't 
want it here also here so that that outline's got   to go no outline all right that looks better 
no outline on that so tax summaries so what   again what i want to do we need to get that year 
well how do we know that year well we have that   year here we know a formula for that year here 
right so we can just add it here and right we know   using the indirect so we can just do that that's 
going to set our year easy we've already said a   year so going back into the income tax all we need 
to do is use the word we've already had this on   the equals which is what i want and right this if 
error so that's going to set our year now we have   the tax number now as we change our year here we 
see that automatically our data is automatically   updated we can increase this a little bit higher 
there so we get all of our data encompassed i like   that it's looking pretty good okay it looks really 
nice so now as we remember i said as we select   a different year we want that to refresh so we 
already set that up in the selection change event   looking really good print summary last thing is 
i want to be able to take all these documents and   i want to zip and email them and create an email 
that's the last macro let's write that right now   zip an email i already have dimensions to help us 
move us along because it's already an incredible   marathon training so let's see what else we want 
to write inside that what we want to do is we want   to make sure that we set that print range right 
just as we did here i want to make sure that   we're setting the privilege not only do i want to 
zip an email i want to create this into a pdf i   want to print this summary into a pdf and i want 
to include this pdf inside the zip file as well   so here's we're going to do first we're going 
to set that print range just as we did before   and also what we're going to be doing is setting 
up that print just in case i'm going to set that   print range just as we did before of course we're 
not going to print it out we're going to actually   create a pdf to do that so but we set the print 
area we did those things those are important okay   i also want to set the tax here i want to know 
what is the selected tax year but i'm not sure   what document's going to be selected so but i 
do know based on a formula that that tax year   is going to be right here inside o3 so i'm 
going to set that into a variable so the tax   year is going to be equal to whatever is in the 
database here our tax doc database dot range   03 that's going to set our premiums whatever's 
there i want to know what that is dot value okay   it's going to be our pr our tax selected tax year 
once i have that selected tax here i want to check   that we have an accurate document folder very very 
important now we did that already up here inside   the documents just to make sure that we had it so 
we can just copy that to make sure that we had it   we did it right here right here to make sure that 
the document folder is correct okay so up inside   when we browse for the document file we wanted to 
make sure that we had document folder right we're   checking to make sure check for accurate document 
folder i want to do the same thing inside so i'm   going to copy that to make sure that we have the 
document folder because that document folder is   going to where we're going to be zipping it up so 
we're going to paste that in here that's going to   check for the acumen document folder it's not 
letting the user browse for the correct folder   okay so once we know we have a correct folder 
i want to set the zip folder that's going to   be a string variable the zip folder is going to 
be equal to the document folder we've got the   document folder make sure we've set it here admin 
range document folder right making sure that we've   set that up properly is going to be basically 
our document folder plus our tax year and our   tax year that's why the tax year is so important 
and i want to add a backslash on to that okay so   that's going to be our tax year okay that's going 
to set the zip folder okay i want to know what   folder we're going to be zipping very important up 
all the documents in that folder must be zipped up   okay now that we have that what i want to 
do is i want to check to make sure that that   to see if that tax document has been created i 
want to make sure that we're we've got a correct   folder there the year so if the directory okay 
of the document folder and i also want to add   the year on to that and the tax year obviously we 
can't zip it up if it doesn't exist and backslash   here vb directory i'm going to check to make 
sure there so we can do just the text here that's   sufficient enough right dot vb directory here is 
equal to empty then we don't have any right we   don't have any director then what did that in then 
right we have no folder to do that there's no year   we must have a year folder to zip up right there 
must be documents inside that earphone message box   let the user know there is no document no document 
folder for the year of what what is that year and   tax year and please add at least one document 
right as soon as they add a document the year   will be created that folder will be created 
add at least one document to zip up and email   no folder no files nothing to zip exit so very 
good now what we want to do is i want to create   that pdf what i want to do in a pdf is i want to 
create a summary based on this basically i want to   print all of this starting starting let's 
say from a print this in a pdf create a pdf   and also include that inside the zip file okay so 
that's just what we're going to do right now we've   already set the print so the pdf file is going to 
be equal to right we're going to set the file path   equal to the this is the string variable equal to 
the doc folder and the tax year and the backslash   and what and i want to set a name i want to set a 
specific name for that tax year that's what i want   to start out with and i'll do dash summary right 
so i want to give it a name dot pdf that's going   to give it a very specific name for that okay so 
that's going to be called let's there we go and   that's called tax summary pdf that's in a string 
variable so that we can create it so once we have   the accurate file path and the accurate file name 
then we can create it okay we just want to make   sure but if it already exists in the folder i 
need to remove it if the same name exists so   if we're going to check here if directory pdf file 
dot let's make sure it's spelled right pdf file   dot vb directory okay does not equal empty that 
means it exists already and i need to kill that   or remove it then kill and then the pdf file 
so that's going to remove the file if it exists   because if i can't recreate a new one with the 
same name remove file if existing continuing on if   i know that it's been cleared out now now we can 
create it so how are we going to do that we can   use income tax that's the sheet associated we've 
already set the print range so that's good now   what we're going to do is we're going to simply 
export it as a fixed format and what format is   it's going to be a pdf right and what is 
that file name well the file name is the pdf   file that's the file name we said quantity of 
course we want to set that as to 1.

We don't   need that that's not necessary and then what we 
want to do is include document properties ignore   print areas we want a false we want to include 
the print area so we don't want to ignore from 2   and then open after publish i want to make sure it 
falls i don't want that opening up that's that's   sufficient for now so that's going to create that 
pdf and put it in that specific file perfect so   that's what we have it in the right place so once 
we've created that i want to set the zip path what   is that path of that zip folder we have the zip 
folder but now i want to set the zip path so that   it's going to be that actual zip file name it's 
going to be equal to the doc folder and the tax   year and right i want to give it a specific 
name dash let's call it documents so we have the   documents dot zip that's gonna give us a zip file 
zip file name okay good so now we've got the zip   file into a string and now we're ready to create 
that okay so we create that now we've got some   outlook applications i've got a shell application 
we're going to create that as an object in late   binding and we're going to create it right here so 
we're going to set that shell app is going to be   equal to we're going to create an object create 
object and what type of object are we creating   we're going to create a shell application this 
is important for creating that zip file shell   dot application so once we've done that we can 
work with that we need to create an empty zip   file that's first thing and then we fill it up 
create empty zip files so the first thing to do   with that is we're going to open that zip path so 
we can do do it with this open first of all i want   to make sure that it's empty so if the length 
of the directory here the zip path making sure   does not equal empty just in case it's not empty 
does not equal zero then we're going to kill it   right then kill just to make sure it doesn't exist 
zip path i want to make sure that it doesn't exist   okay just okay so now we're ready to open it so if 
the length is greater than zero then kill it okay   once we have done that then what i'm going to do 
is open it so we're going to open the zip path for   output as number one okay so it's going to open 
it up then what i want to do is i want to print   number one okay and this is kind of some i'm just 
going to copy and paste this it's going to take a   while i don't there's all these strings that help 
us create the zip file kind of weird but making   sure we get it accurate character 80 character 75 
character five character six somebody will explain   that to me one day close number one okay close 
number one so basically all we're gonna do is   opening it updating it printing it out and then 
closing it okay so now we've created that empty   zip file now what we want to do is we want to copy 
and we want to compress the folder so copy here   and compress files into the folder compress files 
into folder okay so we're good to go so to do that   again we're going to use that shell application 
so set shell app is going to be equal to create   object so we did that already so just to make sure 
we've done that already that's sufficient we don't   need that we can just bring that down here i'll 
bring it right down here we're good to go on that   and then paste that here okay so we can create 
that there once that we've done that we've created   that cell then what i want to do is i want to work 
with it so how do we do that so the shell app dot   namespace okay we're going to create the namespace 
is the zip path zip pap that's the namespace of   that dot items.count we need to determine how 
many items in that folder we're going to zip up   is going to be equal to the shell app we're going 
to count those dot name space zip folder so inside   the namespace zip folder dot items dot count so 
based on how many items are in that folder adding   that copy here making sure that we're copying it 
we need to know where to copy okay great so now   what we need to do is we need to need to make sure 
that we're going to automatically do that until   all the files are so we're going to know here keep 
script waiting until compressing is done script waiting until compressing is done so 
we can do on air resume next okay and   in here we're going to use a do until due 
until until when until the shell application is   the counts are equal to account until we've done 
with all the things so shell app dot name space   and then again the zip path dot items dot count 
until all the items are accounted for okay in the   zip folder so i'm going to copy this here and i'm 
just going to paste it there do until equals and   of course we want to make sure instead of zip path 
this is going to be the zip folder right until   all of the items are accounted for inside the 
folder there so until all the items so we're   going to do that i'm going to loop through all of 
that until that happens okay and then we'll do on   air go to zero just in case there's an error all 
right good so we've got that accounted for good   okay so now we've created the application so of 
course i'm going to save our work and now we've   done so now once the zip is created we're ready 
to create our email so we're going to email it   so we're going to set the outlook app again using 
another object equal to create object it's going   to be the outlook dot application so once we've 
created that outlook now what we want to do is we   want to set the alek email the outlook email also 
an object okay and that outlook email is going to   be equal to here that alec app and then again it's 
going to be dot create item it's going to create   that email create item that's going to create the 
specific email that we want to okay good set email   equal to i'll equal don't forget the equal randy 
okay once we have the equal there we're ready to   go now we're ready to work with it so with the 
outlook email we're going to work with that now   we don't know who we're going to be sending it to 
so we're just going to leave that blank 2 equals   empty and that's going to set our email okay but 
we also want to do attachments right so we want to   specifically attach something so dot attachments 
dot add what do we want to add i want to add that   zip path zip path that's going to be now we're 
going to set a subject subject we can set any   subject we want really it's going to be equal to 
let's call it tax summary for and then a space   and tax year set subject and now what i want to do 
is i want to set the body we can do anything with   that body is going to be equal to i have attached 
the tax summary for year ending let's do and the   tax year okay keep it simple great and now what we 
want to do is just display it i don't want to send   that email i want to display display use dot send 
to send right away so dot display that'll display   it okay let's take a look at that of course saving 
our work fixing any issues and we'll take a look   at it this is the macro that we're going to 
assign here right here so i'm going to copy   this and assign that to the button here so this 
the zip and email so holding down the control   again assigning the macros here assign the macro 
pasting that in here and clicking okay and now   that we've got the macro sign we can select any 
year and then we'll click zip and email and take   a look at that and see let it compress okay that 
looks really good we've got the our tax summary   for your 2018.

Let's double click on our zip file 
we'll open that up check for the documents inside   all the documents associated are inside that zip 
file that's really really nice okay it's looking   really good all right let's take a quick review 
in this particular training i showed you how to   create a brand new list for tax years documents 
associated within that tax year save update add   new delete documents and to show a preview of 
the document regardless whether it's picture pdf   word excel or anything like that showing that 
preview we also created this really cool view   tax summary where we have this graph total income 
credits and income and expenses and also of course   lots of conditional formatting and display that 
record and of course print it along with zip and   email thank you very much for sticking with us on 
this incredibly long but amazing training i really   appreciate your continued support don't forget 
you can support us on lots of platforms including   patreon where you get additional features fixes or 
focus along with pdf downloads advanced copies of   the applications you'll even get downloadable 
videos and of course we have our 200 workbook   zip file for just 77 and now it comes with 
the optional pdf code books inside there so   i've even got that go ahead and check that out 
using the links down below thanks so much don't   forget to subscribe like and share this video 
and we'll see you next week thank you so much you

As found on YouTube