Discover Free Billing Software in Excel for Marketing that Streamlines Your Workflow

Free billing software in excel for Marketing

[Music] foreign you can add your own company logo in your receipt make your own QR code click here you can see your selling product double clicking product name and prices are there together with the picture change it as well picture is changing adding the quantity put them into shopping basket you can select another item double clicking it adding into shopping basket select pay type by clicking it adding pay type amount automatically calculating change as well click the print button purchase summaries are automatically calculating also including your QR code which including your website information you can print a PDF or paper version you have a beautiful receipt like this click next button means next customer receipt number is increasing all other input is resetting it you can change your cashier as well if you go to sale history it's automatically including purchasing information by receipt numbers if you click this button this part is save as as a separate file based on this information we can create sale Trend by hours or day or minutes and also you can check sale quantity by cashiers in addition if double clicking it it's correction mode change to to break back in shopping basket it change into two also the little item by clicking buttons thank you just prepare for subscribers who are using Excel 2016 version if you go to Icon 1 sheet you can see a lot of icons I just prefer for you and it took a lot of time but I hope that this will give you a small benefit please do not worry about it we start from opening Excel file if you cannot understand it just watching the video twice or three times then you will get it let's start it go to view tab deselect grid lines you can give a color of sheet here I will go to Home tab select this color so that's gonna be background of sale we see BBA tool in B1 cell we can put our title bold and make it bigger let's think about information selling product so that's gonna be product name price quantity lastly we need a sum amount of selling product in case of an adjusting size of the cell double click the line it will adjusting it due to this size is too big so you can make it like this way we can think about payment information as well so subtotal which is individual total amount before tax tax some of the item total which is subtotal plus tax pay type cash or credit card paid amount by buyer change let's make it a bit nicer so select here all borders select here all borders double click here all borders as well this is our input make it bigger lip here this is our payment summary let's give a color select here I will give this color white font color Ctrl 1 border select this color that one okay right same thing here these two control one this color select here okay from K to n we are going to make a receive I will give you white color let's edit Header information we select this Ctrl 1 button go to alignment select Center across selection okay button the actual values are still sitting column K make it bigger B make it bigger we need more space so I will make it like this we need another information receipt number dates and cashier for date we need a function which is now I will merge the cell select here and tap we also merge the cell here we will give a name here something like this tool put it here select sheet1 click right hand Mouse button rename Main and enter normally cell A1 you can put your company logger in order to do that please go to insert illustration pictures this device and I will select this and insert but adjusting the size highlight the title in here we are going to put the information of the receipt product quantity price total select all make it bold and Center Ctrl 1 border select this line and up and down okay it looks nice if you click the plus sign you can create a new sheet change the name put product I will select the product information here plus again here rename sale history one more click here we added cashier sale history we collect all the history of our selling cashier is list of our cashier in case of a product list let it prepare it in my case I have the following 10 items price location of the image of each product to be make sure in my case it sounds W here but if you click it it actually reverse slash in case of a sale history prepare the following receive number date cashier product price quantity and total which is like receipt numbers are sitting here and date and Cashers here so that's gonna be here this product quantity price and total that's sitting next in case of casual I prepare the list of the Cash 3 grid line is unselected it let's go back to main sheet here I would like to make a list box go to date data validation select list Source part go to cashier sheet select this line okay button if you click and then you can select different cashier which is much nicer than typing the name of the cacher and in our case from here below it's gonna be roll 10 afterwards we are going to add it a lot of different product informations therefore it will be nice to fix the row so please go to view tab freeze panel freeze panel let's make a PVA alt f11 go to insert click module you got the module here module one let's change the name here F4 button let's put it sale receipt but it has to be one word then close it so let's make a Sub sub shopping basket and enter and automatically ends up is creating let's testing MSG box testing then enter so when you see changing automatically to capital letter which means your coding is correct in order to run PBA click F5 button or triangle button and square button is reset of the yourself Let's test it run message box is correctly popping up so close it let's make it comment edit this one which is not a real coding what we are going to do is product list information for example computer this value going to be sitting here price this 700 is going to be sitting here let's change the format here so go to Home tab and English dollar sign same here Ctrl C alt yes format of course we can copy paste like this manually but let's make it more nicely we create window just click the window and then automatically this value plug it into here go back to the VBA to make user form alt f11 insert user form click make it bigger here by Mouse dragging it F4 button you got user form properties here we change the name as you wish it so product list form enter next toolbar we create header click the image click it like this image property back color black here create text box here then we can put product list go to text line select Center go to phone you can select the font you like so I go impact select it maybe 24 okay color white put it back here go to a special effect click special effect flat go to back Style zero we are done for the header part now go to toolbox and select the list box drag it make it like here and make it bigger after clicking the list box press F4 button we are going to give product sheet information into user form go to role Source here we put product this information from A1 to C11 row sheet we get product dot a one two c eleven enter we got the ID number however we don't have product name and Etc to solve this problem go to column count change to three we have a product ID product name and price however we don't like the header we change this into true we are going to have a headers then we need to change the row Source this is going to be 2 then we can see headers are sitting like this click the user form again go to caption let's change the name as product list it looks much nicer now close it let's make this product list is popping up into our sheet please go to the sale receipt VBA let's create another sub product list and enter button here we are going to call product list form which we made it copy product list form Point show enter Let's test it whether it's working or not F5 is perfectly working run again press F5 button and then I realized Casa is sitting inside of the header and also our user form name is list product list so it would be nice to change the product selection here so let's do it go to here click the text box F4 button first select enable from True to false it's changing it and then going down text part change to selection closet go to BBA again let's run again F5 button perfect now the issue is gone close it so our next challenge will be this product name is going into here product name price going into the price cell close it double click the text box then automatically you will have this text box this part is called object and this part called the procedure so in here if list box is clicking it some coding is activating basically you can take this but in my case I will take the double clicking it which is this one then something will happen remove the previous procedure in here I will add some coding so let's type range Open Bracket C3 and then close it is equal to list box which we made it one in list box and make a list in the list again list the box one Point list index which is here comma 1 which is a column number close it and enter let me go back the list box again double click here this is our list box you can see list box this is our list and list index 0 is here this index one is here and two is here okay close it and go back again double click in the same manner I copy Ctrl C Ctrl v c for that gonna be two which is a price let's make product ID as well create somewhere here P three zero right and close it go back to sell receipt F5 button just click it is a Selection double click magnifier is in the product name price is in there correctly product ID which is a three is correctly applied it here everything is perfectly working but I will put this into column Q closet double click it but I just also realized that it'll be awesome automatically close it right after selecting the necessary inputs double click it in this case we need to unload me that's it we complete this part during creating product list user form was a bit cumbersome to run VBA therefore let's make a button which is a link to the product list let's go to main sheet please go to insert tab click illustration shapes I will take this rectangle drag it you will get this you can see the yellow Dot and moving a little bit make it more subtle let's type it product list list click white circle go to Home tab middle and middle bold make it bigger click right hand Mouse button go to assign macro select product list OK button let's check this button is linked to the PBA code click it it's working properly so close it let's put it here somewhere let's make the button a bit nicer select the button Ctrl 1 go to effect 3D format click here select what you like I will select round it looks nicer in the same manner we can create a lot of different buttons as well in order to copy hold the control button with the mouse and you can drag it you can create another button like here you create another one again in the new button you go here inside and change the name I will create the delete button make it small Ctrl 1 change the color to dark blue holding control button and drag it and make another one clear in the same manner we can create next button or print button Etc in order to speed up I just created like this one important thing is click the button again Ctrl 1 button go to size and properties properties select don't move or size with cells which means that for example later on when we created lip button it will delete the line here let's delete it you can see the clear button is a changing at the moment in order to avoid this situation so we have to set property like that way you can apply same property setting for all other buttons let's try again click click Ctrl 1 button go to property select all move button when you see here is the icon is in it in order to make this you can go for illustration select the icon go to business you can choose the icon which you like it select this one insert button you can see this is popped up which I apply here and make it small grouping together that's how you can make it for somebody who has a older than 2016 version of Microsoft Office prepare the Excel icon collection file please note that all icon in this file copyright by Microsoft just prepare for subscribers who are using Excel 2016 version if you go to Icon 1 sheet you can see a lot of icons I just prefer for you and it took a lot of time but I hope that this will give you a small benefit in icon 2 also you can find another icons as well we create a product sheet which is our database after that we create the product list window which is a user form from here we link to the main sheet input data lastly we link with the PBA button what we are going to do is following from this main sheet input value and product summary value will go into sale receive area in order to do that we will use a shopping basket button the main function will be input values are adding into sale receipt before to go I would like to make another button so go to insert illustration shapes rectangle selecting it putting into here holding up button and then moving it it allows perfect match with the cell size click right hand Mouse button print to front select again right Mouse button again assign macro select product list and OK button go to shape fill select no fill shape outline no outline what we've done is we create another button which is exactly same function as product list button clicking it and it's popping up next select the shopping basket button go to assign macro select shopping basket OK button Now button is linked with BBA alt f11 button so our idea is the product name going into product name here price is price quantity is quantity so starting with range k11 which is that value equal to range is C3 the meaning of this coding is these tools are not equal but this C3 value will moving into K10 value let's test it I will click the button here you can see the bulb is moving from here to here ing to this one line we are able to move from input value into receipt area in the same manner we can create price and quantity Ctrl C Ctrl V so in case price we have put M and that's gonna be four Ctrl C Ctrl V for quantity L that's five delete this one run again it works fine but the quantity is nothing so let's add in another one to run again hit the button it's working for this two line select the currency with the English in case of column C these cells are fixed location therefore we can make it simple by using name box here to use namebox is very simple select the cell adding the name here we put the product name and enter button then you can see this cell has a name in the same manner we can have here price same manner for here quantity some amount subtotal tax item total pay type pay amount change in order to speed up I already given names quantity for quantity some for some amount subtotal for subtotal tax for tax item total pay type paid amount change also product ID here as well alt f11 so we can remove this part bracket product name which we made it and this so let's test it whether this is working click the button perfectly working in the same manner applied for price and quantity as well the next part will be the role here is always changing depending on selection it can be 12 13 14 15 Etc but here is a fixed row so we create the row num which is 11. we can fix it this part 2 then ronum Ctrl C TRL B Ctrl V Let's test it whether it's working or not go here Mouse click shopping basket button perfectly working in the next line next challenge will be this is always selecting the last value in this Row for example like here Ctrl shift Direction down key from the last value Ctrl shift up key it will hit exactly the last value we need to use the name box again Ctrl shift down key select the last cell go here last cell enter Ctrl shift up key again so we are going to create a coding like what I just showed alt f11 remove 11 last cell point and Open Bracket Excel up close the bracket Point row this means exactly representing that value but we need plus one here plus one row num is selecting that value this row information will apply below three lines Let's test it go to view intermediate window question mark copy this line Ctrl C Ctrl V enter button we got 12. which is row 12 here tested it again click here and I will choose Cup this time or click shopping basket button now it's perfectly working here as well what we are missing now is the item total in order to implement this we need subtotal first which gonna be price times quantity and enter button tax gonna be you can choose the percentage which you like so subtotal eight percent I will apply here and enter item total going to be at plus tax enter that's our item total which gonna be sitting here alt f11 button copy the line Ctrl V item portal column n remove everything let's start it again computer 1 quantity shopping basket which is adding it it's including text as well again mouse double click it shopping basket again next let's talk about paid type alt f11 I will create a new Sub sub payment underscore type enter before calling something I can tell you one trick in case of a cells we using name box to make it simple in case of shapes you can also give a name dot name box but selection panel which is go to Home tab click magnifier at the end there's a selection panel click it and then you can see a lot of different things so for example I will create another button illustration and shapes and select one of the box is show rectangle 23 click the button you can give a name here we can give test box enter in the same manner apply this idea into these three buttons and icon as well if I click here this button is called card and this icon is called card the check icon check boxes cache as well close the selection panel here I will show you how to apply this idea into VBA go back to macro in payment type adding pay type equal application point color and holding Ctrl button select three buttons right hand Mouse button assign macro payment type okay buttons and macros are linked let's check how it works click it cache click it check click it card are working simply use this color code which means VBA will called name of buttons in selected cell which is pay type cell there are many different ways but this is the most simple way I believe normally after selecting payment type the next step is paid amount shortly after selecting pay type automatically selecting paid amount sale what we can do is pay type and point select enter let's see how it works choose check automatically paid amount cell is selected by your pay 800 then you can add it like here change gonna be equal to paid amount minus some amount change part is done now we need to implement some amount part that's gonna be some of entire this row right means that some amounts going to be some of sell and tell to cell n11 or the last value of n column go back to shopping basket in case of some amount that is output location so we put it here some amount equal worksheet functions which allow to use function in worksheet so sum bracket from sheet 1 which is main C it one is main G2 is a product Etc Point range Open Bracket and 10 and that and 11. closed close close the bracket and enter Let's test today F5 button the value is correctly applied it so in this case we run shopping basket again therefore one more Mouse has been added our value is a 783 correctly lighted and you're looking at the sum it's 810 the reason why is wrong because we have a fixed value therefore that part has to be changed to change the upper is relatively simple delete the 11 or 2 here last cell control C Ctrl V click again now the values are correct 837 and 837. in case of some amount we use a workship function and sum we apply the range also we reuse the row num idea here as well as the error Handler I will add in calculate which we recalculate the cell values later on okay everything looks fine now it'll be good idea to decorate cells a bit case of quantity this is input go to Home tab fill color Y and bold and middle in case of both sum amount and paid amounts are important let's emphasizing it select the sum amount go to Home tab fill color black font color white and bold paid amount select on Tab fill color purple white color bold pay type bold in terms of error handling perspective what if we don't have information for product name price and quantity this shopping basket adding function will not executing it so let's add in this part go back to macro if product name is equal to empty then exit sub which means that if we have an empty value or product name then we stop this macro in the same manner we can copy Ctrl C Ctrl V apply to price as well Ctrl C Ctrl V quantity as well let's move on to correction sub I will make a correction sub first sub correction enter button the idea gonna be following if we double click the one of the lines here that value is going back to immediately here that will be our correction mode and then we change the value click again and put it back there which means that these values are starting values these cells will be destination cell go back to BBA in order to do that we will using above section copy product names first Ctrl C Ctrl V Ctrl C Ctrl V enter in same manner you copy again Ctrl C Ctrl V that's price that's M for quantity and copy again Ctrl C Ctrl V change to quantity that going to be L the following question going to be how can we Define the row number then this is because the role now I'm going to be just selected by the users here right that is also very simple first we copy this Ctrl C Ctrl V we can put selection Point row the row means is always bring the row information of the given code Let's test it what it means of this selection row so put the cursor in here press f8 button the previous coding is executing line by line so f8 is solid f8 again and f8 if you put the mouse cursor in there it shows the corresponding values which is 19 at the moment go back to sheet our cursor is a sitting row 19 which means that our row num correctly bring the information of selected cell rows square button reset I think it will be good idea to run information is also printing somewhere here therefore I prepared already here the location put here is index enter here I will put malt enter let's go back to macro index equal to row num enter which means that this row Norm information is printing the location here let's test it whether it's working or not first of all I will put the cursor here in 12. drag it down here a bit press f8 now you can see index value 12 so it's correctly reading it reset in case a mode I would like to mention it now we are in the correction mode in order to do that I just copy this line Ctrl C Ctrl V change to mode correction which means that we know we are in the correction mode and is printing correction if not a correction mode and then we can put no correction our correction sub is uh done now the next part is a double clicking it executing correction mode in order to do that Guru May sheet double clicking it it shows like this the object part clicking it select worksheet procedure Parts selecting it select before double clicking it the below part I will deleting it here we are going to add some code go back here if you stop clicking it it's automatically go inside of the cell to prevent this part we need a small treatment that will be this cancel part Ctrl C Ctrl V equal select Rule and enter the next part is we don't want to delete any information above row 10 right these are all important information so let's add in error Handler here in this case we can use a Target if Target Point row information smaller than 10 then X is sub enter let's add one more line Ctrl C Ctrl V delete this part equal this if Target is empty then also exit the cell finally we can call our correction sub call our salesip Point select the correction enter we are done for this part as well let's test it whether it's working or not I will delete these two part first of all let's see whether computer is working I will double clicking it double click perfectly going back to here then I will change the quantity to 2 Enter put it back into shopping basket I got the error here put l it should be fine save it try again and add in shopping basket the result shows that it's not correcting it but adding as a new item which is not perfect yet let's change that part go back to macro I think the reason is because of this line because we are always adding one line there ronum is always increasing it so we have to make it two different conditions therefore we need to use if statement if correction is user in the cell then ronum is equal to index enter else is the below cases and if the meaning is following in correction mode once we are in the correction mode this mode is automatically correction mode therefore our if is executing as a true we are going into this part we already know the selected row by selection row information which is row num row num is index that index value is become a new row num which will apply below lines if not a correction mode We are following same as before next part this mode is going back to default setting which is no correction mode Ctrl V put no in case of index also has to be changed so copy this part Ctrl V here which means in case of adding function the row Norm is always indexed and this value is changing it select these two line tap select these two line and tap which is inside of this if statements so we distinguish correction mode and add mode Let's test it whether it's working or not go back here and I will delete this part now I'm going to use Mouse double click it it's put it back here changing the quantity to 5 and enter adding it to here now perfectly working instead of adding below lines here the reason why I use index and mode is because there is no defined thing ID numbers in the our receipts let's move on to clear button go back to VBA make a new sub clear underscore settings enter button this part is pretty simple move this part here a bit the main idea is all input values become an empty cell in order to do that we using name box like tier and equal empty and enter in addition correction mode become a no correction as well which is our default setting so we are done for it next color sheet link button with the vva click right hand Mouse button assign macro clear settings okay let's check it click it maybe it's a good idea to remove this sum amount as well copy this part Ctrl C go back to macro here comma Ctrl V try again it's better let's create the sub first go back to macro sub delete enter let's define raw sum first which is a selection [Music] Point row because user will select one of the lines here rows row num closed bracket point delete so we complete the leave function go back to main sheet let's link it click right hand Mouse button assign macro delete okay I will delete the mouse selecting press delete button delete function is perfectly working however the sum amount which is 772 dollars but we are still 907 dollars we have to change this part go back to macro then using this idea some amount Ctrl C Ctrl V copy Rawson Ctrl C change this part to Rosa let's test it go back to sheet select the computer delete button we can see some amount is perfectly working as error Handler perspective above row 10 shouldn't be removed because it's including a lot of necessary informations let's add in error Handler for this part back to Macro for error Handler we have to create somewhere here we can use sheet1 information double click sheet1 copy these two lines Ctrl C back to the list stop Ctrl V we don't have a Target but roll num Ctrl C Rona Ctrl V for a second part we can reuse this format Ctrl C deleted it change it to L which is quantity delete the number Ronan closed bracket it means that row L which is a quantity that row if the value is empty delete function won't work in the same manner we can make product price total as well I just create additional three lines increasing back and as a last step let's call clear setting copy this part call clear setting and enter we complete delete function Let's test it I will select football press delete button everything become NP let's test it one more time I will erase this part select this row press delete button and it doesn't work so our error Handler is working as well it will be good idea if we select a row automatically highlighting it in order to do that first select the range Ctrl shift down go to Home tab conditional formatting new rule select use a formula to determine which cell to format equal row Open Bracket K 10 press F4 button three times one two three close the bracket which means that our column K is fixed equal to index if column case row information is equal to index then we apply follow format go to format select bold color white fill blue okay okay back here as you can see row index 10 which is 10 that row highlighted if double clicking it in the exchange to 12 and also the row is highlighted the next possible problem we can have is if we deleting a lot then like this row is deleting it and then the color is shorter and shorter keep moving on so in order to prevent this situation let's add an additional function we are going to use the right cutting function go to developer tab record macro OK Ctrl C of this range up to here Ctrl C click A10 button and then drag it down to let's say 30 alt e s select format Okay click p8 go back to developer tab stop recording go back to vva now you see module 1 is creating it so if you're clicking it there's a lot of things are happening go back to say receipt module create a new Sub sub clear formatting enter go back to module 1 copy this part Ctrl C Ctrl V delete this point tab then we can add sheet one point Ctrl C Ctrl V so the idea is very simple sheet1 main sheet select the range of this part copy it from cell A10 to u30 rows paste the special which is only format and after that we select a p8 it's all press Ctrl button and create one more line I will create a clear format button click right hand Mouse button assign macro clear format okay I forgot one more line go back to vva we need a disk line which means application Cut Copy mode is force this copy the range is deselecting it so we finished clear from a part as well we create adding basket which is adding function after that product list payment type correction mode is edited clear function delete button lastly clear for mess up what we are going to do is following photo editing function print function store and next and save as file function as well let's continue as next challenge we are going to create the add photo here depending on selected product we already prepare our images location we are going to use this information go back here we are also using product ID for selecting folders here as a recall let's go back to PVA go to forearm double click double click here and look at the Q3 which is a product ID when we double click the product list form the ID product is a change to be clear I will change this part into product ID that looks more nicer better also I change these two lines as well go back here in shopping basket in correction mode we also change product ID as well depending on selecting rows therefore product ID is equal to sheet 2 which is product sheet entire column B Point find product names close the bracket and row it means that in product sheet in entire column B we are searching for product name that's raw information but here we are having header therefore we need minus one now let's add in photo adding sub sir portal edit with sheet one and wait which means that we are going to add some coating which only relevant for Sheet one if directory Open Bracket path name which is sheet2 range D then we need row information then product ID plus one due to the header closed bracket value comma the EV directory than this which means not equal to empty cell then so we are searching for directory in product sheet column D with raw information then with pictures which is another width inside of a weed and and with select these two Tab Key inside of we are selecting picture also we need and if here now inserting what we are inserting entire this point Ctrl C Ctrl V so you need a space here by the way in inside of the selected picture we create another width and and width wheat shape range so we are defining the range of shape creating and width select tab key again in the shape range we are having this called which means locking the shapes ratio true means that it stays original proportion when we resize it so let's go back here select one of the shape Ctrl 1 property go to size click or unclick is lock aspect ratio give a height 110 around 3.86 centimeter width is same so right Square means that again height is 3.86 width is 3.86 centimeter then also give a name for it this part will be home tab selection panel giving name like card select again Tab Key so we complete the shape range settings now we are going to define the location of the shape that's gonna be E3 we need another width with the shapes and width what we needed is this part so left side is the in sheet one main sheet range E3 top part also E3 and top so we Define the top and left we need a Discord which means the shape has to be visualizing let's move here Let's test today whether it's working or not I will select smartphone quantity one press f8 so we can see photo here press f8 again adjusting the size then we got the error this line is problematic Ctrl X put it here and enter now I see we have to delete this photo manually so we will upgrading that part later on press f8 again [Music] size is adjusting it now location it's working seems like in right location now so the idea is following first we create if statements after that we create the insert picture in inside of this web we set up the picture format then select the location lastly highlight the picture let's add in delete function in Portal adding sub first I will introduce to you one nice formula which is resumed next means if we got the error still we continue to go next round in case of a clicking photo or not it's not really bothering the main functions therefore it'll be good to introducing here and second why don't we use the recording function again go to developer tab recording macro OK selecting it deleting it develop tab stopping it go back to macro we have these two lines Ctrl X Ctrl V change this into sheet1 all right let's try it again in this time we assume we already have an existing folder f8 button select and delete and password again it's working properly it would be nice to have a highlighting line around the photos let's add in that part by using recording function again go to develop tab record macro OK selecting the photo Ctrl 1 button field and line select line solid line outline color blue 2.5 select cell B9 developer tab stop recording it back to macro change this bar into sheet1 I think we don't need these two parts we don't need this part either space space again copy them all Ctrl C go back here tap key Ctrl V let's make it a bit nicer just Ctrl X this part Ctrl V delete this part it looks much cleaner save it let's test it again this time I will choose computer press f8 now we are starting the new coatings it's working perfectly fine now we finish the photo editing part as well after completing photo editing sub we have the link with existing part please go to user form here double click here here we give a call photo editing that's it so save it the main idea is this part going to be printing it as a starting point you can add company logo here so I just use it this logo for this example as a Next Step it'll be good idea we have a summary of the payment not this payment but whole receipt therefore we have to use it some amount let's use this part sum and before tax Eco button take that value and enter from here to here Ctrl C here add es value okay go to here item part and okay text gonna be some amount time eight percent in this example total is sum plus tax enter pay type gonna be that value paid amount connect the same as here change is to that value selecting all Home tab click here English let's give a new name selecting all summary so that value going to be below here erase it first then come here let's restart it click valve one adding here add again cup and adding again pay type cash amount 15. Let's test it so our values are correctly printing here as well let's create a new sub let's go back to macro before to go for clarity I will make this part is more clear format so select here click this button and it's going to front it looks better here I create a sub print receipt what we are going to do is copy receive summary printing below part of the receipt for defining summary location we need a row num let's reuse this part we don't need the one line below so here Ctrl C Ctrl V Next we don't want to print the highlight color to remove the Highlight is a simple make empty this index cell then it's gone therefore index and this highlight part is gone now copy receive summary Point copy enter go back to shade so we are copying up to here printing two lines below here column M alt e s value okay so from here to the end [Music] go to Home tab line right from here to in the end due to cash part center now deleting it make it in VBA here so main sheet column M row information so row num to write below plus two closed bracket Point move a bit here then paste the special as values perfect go to clear format copy this part Ctrl C paste it let's have a error Handler if some amount is empty the macro will stop or paid amount is smaller than some amount then we will give error message for that we need if statements and message box if some amount is empty or some amount is greater than paid amount then message box we can say no paid amount has to be higher than some amount and if and also need to act this up as well let's test it f8 copy paste it remove the copy selection it would be nice to have a footnote thank you for purchasing and QR code let's create a QR code first go to cash your sheet I already prepare my QR code which is my channel information go to insert tab add in get add-in store here QR for Office enter you can have this click add button in my case it's already there so add in my add-in already have that one if you click in it you can have this side window copy your website information Ctrl C and go to here Ctrl V enter correction hi click insert you got your own QR code which including your website information let's close it Ctrl C go back to our main sheet Ctrl V here now now I'm going to create the thank you part insert illustration shapes rectangle make it like this type the message you want to adding then go to Home tab middle middle bold bigger as you wish and then I will make bigger this part of it click again on Tab font color black Ctrl 1 button fill no fill line no line close it holding Ctrl button and click the Box sure format align Center make a group go to Home tab here selection panel this is a selected one let's give a name footnote enter the name for this is foot Dot the next challenge this part will be below receive summary right back to macro let's reuse it from photo editing copy this line Ctrl C then we can selecting it Ctrl V here change this part to footnote copy one more time Ctrl C Ctrl V then we can also go up here copy this part which is visualize it true first footnote is visualizing it we select the visualizing one after selection we can Define the location of footnote so selection left equal to sheet 1 K column we take the row num information adding seven more plus Point left enter copy again Ctrl C Ctrl V that's gonna be top that's also top here will be 10. next we will Define the printing setup the first location of printing sheet 1 setup print area column K1 to end row num M plus 18 more sheet 1 print out comma three comma more and true and 4 comma more [Music] false enter lastly our cursor will be cell P9 if you want to printing as PDF file print Microsoft printed to PDF then you will get PDF form or you can select actual printer then it will print it in paper so I will set it up as a PDF form let's go back let's highlight this part a bit nicer so I will emphasize total part just select the location like here go to Home tab conditional format new rule use a formula to determine which cell to format our new rule will be based on column m equal M10 press F4 three times one two three equal that portal and this exactly same as that part and then click format border select up and down font bold okay okay let's make another line select the range Home tab conditional formatting new rule select here equal to M10 F4 3 times 1 2 3 equal that temples sum this part and this then go to format border I will take that one on the upper part okay and okay it looks quite nice now as additional information this part is empty and the conditional format will be gone same for here as well delete and disappearing put it back let's test it go back to VBA we already know here is working so make break point click here F5 already up to here is done then f8 that part is gone I paid again f8 f8 f8 I will move here and print and selecting it click the dot the breakpoint is gone open the PDF receipt we have a beautiful sale reset here the next step will be link with button which we created before go back here click the button was a macro print reset okay let's move on to the next function which is a next button which means that one customer is complete his payments and we are going to restart everything from scratch the main idea of this function is following once we click next but all this area become empty second the detailed information of the sale receipt is going into sale history sheet and store here by receipt number the detailed information has a two-part receipt number day Casher secondly product quantity price and total part let's give a name for these three cells by using namebox click here M5 I will give you receipt num enter here date here for cashier enter next create the new sub go back to vva here are some store and next enter as a starting point we need a new row num which is based on the sale history sheet let's reuse existing one I will copy this part Ctrl C Ctrl V but we need a new last cell so let's put it last the cell to Ctrl C here go back to sheet go to cell history and click here Ctrl ship to Direction down key click the last cell control V last cell to and enter go back here go back to VBA this part has to be row num2 also we need the existing Runner as well so copy this part Ctrl C Ctrl V here in case of row number two we have to edit plus one because we go next lines let's create the new variable which is a alt item ronum minus 9 enter what it means back to here one of the last value here minus 9 rows here next part is grabbing this information and plug it into this sheet so we need something range like this way as a reminder in shopping basket we built this idea that one is moving into here our receipt number will be here the destination gonna be sheet 3 which is the sale history go back here Ctrl V we are going to put sheet 3 receipt number will be column a a and row num 2 which we defined here and still column a right so we are up to here and row num 2 plus all item minus one because of the header row close the bracket point value equal receipt num enter it is still red something is missing so I think here we forgot this enter receipt number will move here but not single value but range we can apply this idea to date and cashier as well copy entire line Ctrl C Ctrl V change to B change to B change to date cash here is same idea this part is done now let's move this part we can still use this information Ctrl C Ctrl V that's gonna be D this part will be G that means from d to g here the other part is from sheet 1 which is main sheet our range will be K2 and rho num close the bracket point value the meaning of this part is from K10 here is fixed value we start from here until the last row column is n that is the meaning of this part Let's test it before starting I will put dummy number 100 and enter as a starting point then I will move to cell history I will move around again press f8 receipt number is coming dates coming cashier and product information back here so all these informations are correctly applied accept button moving information part is over the next part is resetting the existing informations let's hide in footnote for first go back to macro let's reuse it our existing code I will copy this line Ctrl C select the footnote then Ctrl C here this part Ctrl V make it to false which is disappearing delete false and let's see whether it's working make a breakpoint here click press F5 f8 selected it press f8 disappearing removing breakpoint now let's clean up this part for removing this part is let's using clear function in order to do that let's using the inbox again Ctrl shift to right direction key and down Direction key again go to name box I give all receipt as a name and enter go back to VBA we give or receipt Point Clear contents it will erase this area and enter call clear setting now we need to remove this this photo as well let's reuse it existing code go to photo adding copy these two lines Ctrl C TRL V here the next step is we will update this receipt number for next payment in order to do that let's go back to sale history click here control shift down key even name all rows to enter go back to main sheet PVA this receipt number is our destination so we copy this part Ctrl C Ctrl V equal worksheet function Max from all Rows 2 which we created closed bracket adding one number more enter let's link it to the button with the next button click right Mouse button go to design macro select store and next I prepare a new example for testing press next button and we cut the error here let's check its clear settings let's add s here reset next button photo is disappearing receipt number edited and this content has been disappeared let's check the cell history receipt information stored correctly as well here as the error Handler perspective after putting into shopping basket without clicking print button I just click the next button let's see what will happen we got the error because we have to hide food out there is no footnote therefore it create error in order to avoid this part we can copy this line Ctrl C here Ctrl V reset save it I will click the next button again the other thing is that blue lines are still there so we have to fix that part as well Guru clear settings in VBA here I will Define index equal to empty which means index part will be disappear like this we complete next Button as well before move on next function let's have a cleanup go back to BBA I will select here click right hand Mouse button remove module one click no I just remove unnecessary modules our next function will be if we keep storing and it will be gigantic let's save as cell history as a separate file therefore I create another new buttons let's create a new sub save as enter and then crochet click the button as I macro save as file ok go back to VBA in this time I will show you how to declare variables for that team WB as workbook enter theme WS as worksheet so we Define workbook as WB worksheet as WS theme last row has integer enter Ctrl C Ctrl B start column enter Ctrl C Ctrl V stop row we create three more integers the main idea of this function is the following basically we copy all this information and save as new file the location of a file will be as same as this file also you can Define the name as well file name it including the date of creation of the file as a starting open a new Excel file let's start with the set WB equal to application Point workbooks add enter set WS equal to WP Point worksheet 1 enter next let's select the starting location of the results move here a bit that gonna be G3 cell history with sheet 3 and with code inside here start call will be first the column so one start row will be second last or will be Point cells in sheet 3.0 Point rows count comma start call close the bracket Point XM up Point row Point range again Define the cells like above sentence start rows comma star call comma so we select the range now now cells again last rows comma and star code plus 9 closed bracket then copy space WS which we defined and range cell A1 enter next we will save as a new file WB Point save as space this workbook pass W but this is a backslash in your case and I will put this part that gonna be file name space And format now comma space that format and enter so save as the location as this word worker slash file name will be sale history underscore then we add in this format of dates will be together as file name active workbooks close active window means closing the new file Let's test it how it works we'll move here press f8 started it and open new file then we got the bug from this line so we are missing the point here put Point try again press f8 we got book for now press 8. we copy the necessary information press f8 again so the previous file is closed go back to Sure folder now you can see new file is created let's open it we can successfully create as a new file only for sale History part looking at the header of the table I just realized that quantity and price are wrongly placed it I will exchange it it'll be a good idea to have a statistic of our sale activities in order to do that we need the following steps select cell A1 Ctrl shift direction right key down key holding Ctrl t button create table okay put the cursor into the table go to insert tab select pivot table from table and range click OK we have a new sheet here I will change the name rename payback for row I will put the date drag it and drop it column I will put products for values I will put total amount close it for now let's remove grand total I will put inside and click right hand Mouse button go to pivot table options totals and filters deselect Grand totals okay magic cleaner now select entire area Ctrl 1 button border make a border here okay make this area go to Home tab select English decrease decimal by one go to view tab deselect grid lines we already prepare our pivot table let's give a name for this part sale Trend by hours Let's Make a pivot chart first put the cursor inside of the pivot table then go to pivot table analyze pivot chart you can choose the chart which you like I will go to line select line with markers and OK button I would like to change this part which is a Korean setting at the moment but actually I don't know how I can change it I try the following way select here and then click right hand Mouse button go to format cells numbers dates and select this one double clicking it or I try the location to United his States still it's the same sorry about this this is months this is the put the cursor inside of the pivot table go to pivot table analyze select keyboard chart you can choose the chart which you like I will go with the line and select with line with markers and OK button put your cursor on top of the button click right hand Mouse button again go to hide all fill buttons on chart after that decimal one more time Home tab and decrease it select the chart click the plus button chart title go to formula r equal sign select the location of the chart title Ctrl V Home tab select the color which you like blue for now select the chart again plus sign remove grid lines select the chart again go to format shape field no fill shape outline no outline the charts are more cleaner and click the lesson Ctrl 1 button put them bottom close it here again make it bigger if you go to days you can actually extend it to deep more detail levels if you click one more time it goes by hours and click here hours as well here hours as well the beauty of this part is Pivot chart is also correspondingly changing it here so you can estimate by hours of your sale activities for each product as a practice let's make another table selecting all Ctrl C enough space Ctrl V put the cursor inside pivot table analyze show select field list put it back the dates let's go with the cacher I will put it here deselect the total let's try with the quantity this time close it that is not a dollar and then select here Home tab comma style let's give a name here cell quantity by cashier put the cursor inside right go to pivot table analyze pivot chart select column clusted column Okay click right hand Mouse button hide all fill button on chart select here plus sign adding chart title removing grid lines select chart title equal select the cell which has chart title control V color blue select a chart go to format shape fill no fill shape outline no outline select the lesson Ctrl 1 bottom close it make it wider select vertical axis deleting it click each series click right hand Mouse button add data label do same thing for all we can see which capture has more sellings so you can create all different types of people chat as you wish we can create another new sheet just create a statistic sheet here select the chart control X go to statistics sheet Ctrl V same for another chart Ctrl X Ctrl V then we can have two different statistics as well as long as we have a new cell history here we can also update very easily for updating this part is also quite simple go to data tab click refresh all button pivot table statistics are automatically updating it in order to avoid the refreshing button we can also add in this part into VBA alt f11 go to store and next and then adding this line which is active workbook Point refresh all pivot table and charts are automatically refreshing it we did it I hope that this file gives you small benefits to you thank you for watching