Collaborate on Fully Automatic Invoice in Excel Download for HR with Ease Using airSlate SignNow

Fully automatic invoice in excel download for HR

in this tutorial I'm going to show you exactly how you can create a payslip like this and you can make it fully automated so whenever you press a button it will create PDF file for each employee and for each payslip okay here is how it will be done click on the button payslip will be changed and PDF will be created let's get started [Music] first I need to create a payslip design then I'm going to automate that with our data in the data worksheet so we have data here and here we are going to create our payslip once we have created our payslip we will automate it with our data and then we are going to automate it so that we can create a PDF copy so let's select this cell and merge it and then I'm going to write the company name okay and I'm going to increase the font you can do it ing to your need and I'm going to bold it and so this is our company name and in the next row we are going to merge it again and we're going to write here Company address so we have our company name and then our company address okay now we're going to leave the next row empty and here we are going to write employee ID [Applause] and then we're going to write salary period here we are going to write employee name [Applause] and designation [Applause] now we are going to write all the additions and here we are going to write all the deduction okay so let's resize this column and merge this one margin Center and this one margin Center and let's resize it again so this resize and this format you will have to do it ing to your need ing to your design okay so we're going to list all the additions here so in our data we have additions for basic overtime and convince so I'm going to write this basic overtime conference and E node deduction we have Provident fund loan and tax [Music] okay now leave one row and we're going to write total additions [Applause] and here total [Applause] deductions and now we are going to write here net 7. okay so we have this as our payslip now let's design it a bit so we are going to select all these and I'm going to bolt them and I'm going to Center them okay and I'm going to select this and add total borders here and these cells and add border here now here we are going to add thick powder same is here okay now let's add our logo insert picture this device and from the desktop I'm going to add this I want to add this logo here okay okay I have added the company logo here and let's add signature here so I'm going to select this I'm going to add a thick bottom border and I'm going to select the next row and margin center right here signature and I'm going to add a digital signature here so I'm going to click on insert pictures from this device and I'm going to add this signature okay now I will have to resize it and I have placed it okay so if I press Ctrl P which is a print preview you can see this is going to be our payslip okay you can design it ing to you need if you don't like this setup you can always format it ing to your name now let's write our formula here okay now first here we are going to write our employee ID okay but writing it all the time it it is troublesome so I'm going to create a drop down here okay so click on the data tab select this cell and then click on that Tab and click on data validation and from here select list okay and then from The Source click here and go to the datasheet and select all the employee ID you have okay and click ok so here we have all our employee IDs okay we can select it from now drop down okay let's Center this yeah now we need to automate this oshits okay so whenever we select our employee ID I want our employee name should be placed here salary period here designation and all the addition all the detection and this should be fully automated so I'm going to select the employee name here and the next cell of employee name which is E5 okay now I'm going to write here equal to and then I'm going to write vlookup okay we're going to use vlookup function here so I'm going to double click here and our lookup value will be our employee ID which is in cell C5 okay now our table array is in the data sheet and I'm going to select the entire table here okay now our column index number will be First Column okay and we are going to add exact match so we are going to select false here okay if I close parenthesis and press enter oh yeah we have our employee name in column 2 so we are going to add column two here press enter so this is our name employee ID 1001 name is John Doe one zero zero two is Jane Smith and here is John Doe and Jen Smith okay so our employee name is automated also in the cell C5 we want to fix it okay I'm going to add dollar sign before row and column or you can press f2 on your keyboard now this cell is fixed okay so our lookup value will always be our employee ID and we get our employee name now I'm going to copy the same formula I'm going to place it here in the designation basic so basically I'm going to add vlookup to all the cells that requires update okay so these should be our specific data so our employee name is in column 2 which is here our salary period is written in column D which is 4 okay here I'm going to change it to 4 if I press enter our salary period and we need to format this cell so I'm going to right click here and format cell custom and here I'm going to write m m okay hyphen here here okay so June 23 okay now it's formatted our designation is in third column so here we are going to change to 3 and press enter so this is our designation our basic salary is in one two three four five basic in five over time in six and convincing seven basic in five over time in six convincing seven okay now our Provident fund is eight then low 9 and text ten these are the column number okay so here we need to write eight eight here we need to write nine and the last one is 10 okay so if I change the employee ID from here our employee name designation salary period basic and total addition and total directions are automatically updated now let's format this I'm going to select all these and Center it this is the first part and let's add currency to this so I'm going to make it accounting number format okay so we need another function here I'm going to write equal to and then I'm going to write sum and I'm going to select all these close parentheses press enter so here is our total addition and I'm going to write the sum function here right equal to and then sum select this close parenthesis press enter here are all the deductions okay now here next salary we're going to write another formula which is equal to cell C 13 minus so e13 which is total addition minus total detection and here is our net salary so our automated system is ready we are getting our data if we select it here it's automatically updating okay now if I press Ctrl P it will give us the print preview and I like this format if you don't like you can change it okay now we need to automate this so that we can create PDF file based on each name okay so we are going to write Visual Basic code and for that you need to have developer tab if you don't have developer tab check out this tutorial once you have added your developer tab click on developer click on Visual Basic and then insert and can then module now we need to write our code first we need to write our VBA name which is sub I'm going to write Here Pay sleep PDF so this is our vpnm so if I see passive PDF then I will understand that this macro is used to create payslip okay so name it ing to unit now or N Sub is automatically placed we are going to write we are going to add a loop here I'm going to write 4 I equal to 2 2 21 okay now why did I write 2 to 21 if I go to our worksheet and in data tab we have employer ID from cell A2 to a 21 okay so I want to Loop this data through each employee ID so if it's start from second row I have added Row 2 and it will last until row 21 okay so we are going to take all the employee ID from our version okay now let's write we have to understand we're going to take data from the data worksheet which is in sheet 2 and we are going to add it in the pay slip and we are going to place it in cell C5 where we have our employee ID field okay so we are going to get the data from the data worksheet and paste it in the employee passive worksheet which is sheet1 okay so in the code we will have to write sheet 1 range so what is our cell reference here our cell reference is here C5 okay we are going to write within double quotation C Phi so we are going to change the value of range C5 from sheet1 so we are going to add dot value right equal to so what will be our value so our value will be taken from sheet 2 so I'm going to write here sheet 2 Dot cell from which cell we are going to take it okay within parenthesis our row index number will be I because this is our variable and our column will be within double quotation a so sheet 1 range C5 value will be taken from sheet to our row number will be changed ing to our variable and our column number will be this one okay now it will take the data and place it in this range now we are going to create a PDF copy okay and we are going to create a PDF copy of our payslip which is in sheet1 so we are going to write here sheet1 again Dot export as fixed format then space we are going to write XL and then we're going to double click here Excel PDF and now we are going to press comma so we need to create our file name so once we create a PDF we need to create a folder where we are going to save our PDF so we are going to push that inside the file name so we have our payslip folder so basically I want to set the PDF copy of my pay slip in this folder so I'm going to click here and copy this file path okay and I'm going to paste it inside the CVA okay and add another slash here okay so here d slash payslip slash and we are going to enclose this within double quotation now I'm going to add another Ampersand and then I'm going to write the employee ID okay so I'm going to copy it from here so our employee ID is in sheet1 wrench C5 so I'm going to paste it here okay so our PDF file name will be our employee ID and then I'm going to add another Ampersand and then I'm going to write within double quotation dot PDF this is our file extension okay so in the file name we have three things first we have our file path within double quotation then we have the cell reference where we have our employee ID and then we added dot PDF within double quotation okay now we are going to write here next it will get the data from sheet 2 and paste it in cell C5 in sheet1 then it will create a PDF and then we are going to show a message box which is msgvox within double quotation PDF has pin created okay now let's close this now we are going to create a button here we don't want to create a button in our worksheet so I'm going to add a VBA button to our quick access toolbar okay so from this drop down I'm going to select here more commands and from macros and select payslip PDF which we just have written okay edit it and let's modify it and let's select an icon here okay select this icon so here we have a button and it says payslip PDF so this is our macro button so let's minimize this and this is our rule book and this is the folder where I want to paste all the employee payslip in PDF format so I'm going to run the macro from this button if I click here okay so here all the file name has been changed and we have created all the PDF files and if I click OK it's done if I open the PDF file and you can see company name company address employee ID name and this is our payslip okay this is what I wanted to show you thank you thanks for watching see you in the next tutorial if you can support the channel through patreon and don't forget to subscribe please share this video with your friends and see you in the next tutorial