Home
Videos uploaded by user “Dinesh Kumar Takyar” for the 2015
Get data from multiple sheets in multiple workbooks into master workbook with vba
 
05:35
How to get data from multiple worksheets in multiple workbooks into a master workbook with VBA automatically in the correct sequence. Details available here: http://www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/
Views: 36310 Dinesh Kumar Takyar
Improved vba code to copy data from multiple worksheets in multiple workbooks into master workbook
 
04:40
Improved vba code to copy data from multiple worksheets in multiple workbooks into master workbook.
Views: 18521 Dinesh Kumar Takyar
Automate Copying of Column Data from Sheet to Sheet Using Excel VBA
 
13:02
How to automate copying of column data from one worksheet to another using Excel VBA. Complete code and sample file available here: http://www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/ Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU
Views: 235963 Dinesh Kumar Takyar
Get Value from Closed Workbook with VBA
 
07:29
We can get a value from a closed workbook automatically using VBA. Earlier we learnt how to get data from another Excel file using a manual method: http://www.exceltrainingvideos.com/get-external-data-from-another-excel-file/ More details available at this link: http://www.exceltrainingvideos.com/get-value-from-closed-workbook-with-vba/
Views: 35489 Dinesh Kumar Takyar
Automatically Password Protect Excel Worksheet and Lock Cells after Data Entry using VBA
 
06:36
How to automatically password protect an Excel worksheet and lock cells after data entry using VBA so that the data entry operator cannot make changes without a password. Complete VBA code and sample Excel file available at this link: http://www.exceltrainingvideos.com/automatically-password-protect-excel-worksheet-and-lock-cells-after-data-entry-using-vba/
Views: 42236 Dinesh Kumar Takyar
VLOOKUP Using VBA
 
21:23
How to use VLOOKUP with VBA and automate calculations. Details: http://www.exceltrainingvideos.com/vlookup-excel-vba/ For more knowledge read the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V If you are from India you can get this book here: http://amzn.to/2jzJGqU
Views: 220072 Dinesh Kumar Takyar
Create splash screen with excel vba
 
05:24
How to create a splash screen using Excel VBA. First create a splash screen using a user-form. Let the user-form appear when the workbook is opened. As soon as the user-form is activated and the splash screen shows up, use a timer to allow it to be seen for a few seconds. Then call a macro that closes the user-form splash screen. Details: http://www.exceltrainingvideos.com/create-splash-screen-using-excel-vba/
Views: 12464 Dinesh Kumar Takyar
Get Data from Access Database using Excel VBA
 
08:10
If we can automate the process of getting data from an Access database using Excel VBA, we can do more powerful analysis. Earlier we learnt how to get data from an Access database using a manual procedure: http://www.exceltrainingvideos.com/get-external-data-from-database-like-ms-access/ More details available here: http://www.exceltrainingvideos.com/get-data-from-access-database-using-excel-vba/
Views: 37033 Dinesh Kumar Takyar
Date Time Picker Tool using UserForm
 
06:55
How to make date entries quickly and easily in an Excel worksheet using a date and time picker tool or control inserted on a user-form. More details available at this link: http://www.exceltrainingvideos.com/date-time-picker-tool-on-user-form/
Views: 39225 Dinesh Kumar Takyar
How to transfer data to invoice template automatically with Excel VBA
 
08:56
How to transfer data to invoice template, perform calculations and save invoice sheet in Excel and PDF formats with Excel VBA Details available here: http://www.exceltrainingvideos.com/how-to-transfer-data-to-an-invoice-template/
Views: 13141 Dinesh Kumar Takyar
How to display files in folder with VBA
 
06:40
We can display all files or specific files in a folder using VBA. We make use of the in-built directory function. Details available at our website: http://www.exceltrainingvideos.com/display-files-in-folder-with-vba/
Views: 4700 Dinesh Kumar Takyar
How to use sumif function in VBA
 
13:14
How to use the SUMIF function in VBA. The SUMIF function is useful in inventory management systems, for example. We also show a looping process to automate the SUMIF function. Details are available at this link: http://www.exceltrainingvideos.com/how-to-use-sumif-in-vba/
Views: 23598 Dinesh Kumar Takyar
Automate Calculations of Net Working Days with VBA
 
04:55
How to automate the calculations of the net working days with VBA between two dates. These calculations are useful in project management. Details available at this link: http://www.exceltrainingvideos.com/automate-calculation-of-net-working-days-with-vba/
Views: 3260 Dinesh Kumar Takyar
How to get multiple values from closed workbook using VBA
 
05:18
Using a simple 'for next loop' we can get multiple values from a closed workbook for further analysis. You can get the complete VBA code here: http://www.exceltrainingvideos.com/how-to-get-multiple-values-from-closed-workbook-using-vba/
Views: 8747 Dinesh Kumar Takyar
Automatically Open Specific Page in PDF Document using Excel VBA
 
04:51
How to open a specific page in a PDF document automatically using Excel VBA. Complete VBA code and sample file available here: http://www.exceltrainingvideos.com/automatically-open-specific-page-in-pdf-document-using-excel-vba/
Views: 17878 Dinesh Kumar Takyar
How to update navigate records in Excel worksheet with user-form
 
08:53
Adding records or data to an Excel worksheet is easy but navigation and updation with a user-form is not so easy for many users. The concept of the scope variable makes this quite easy as shown in the training video. For details you may like to visit: http://www.exceltrainingvideos.com/update-navigate-records-excel-worksheet-with-user-form/
Views: 20588 Dinesh Kumar Takyar
Why it is important to define variables with Dim in Excel VBA
 
05:36
It is important to define the data types of the variables used in the VBA code because the speed of execution of the macro becomes much faster especially if you have many variables and need to perform thousands of calculations. Our simple example demonstrates how the speed of the macro with defined variables is almost three times better than a macro without defining the data types for the variables using the 'Dim' keyword. Details available at this web link: http://www.exceltrainingvideos.com/why-it-is-important-to-define-variables-with-dim-in-excel-vba/
Views: 11977 Dinesh Kumar Takyar
Send Excel file as PDF automatically via Outlook
 
08:57
How to send an Excel file as a PDF document automatically via Outlook using VBA.
Views: 18332 Dinesh Kumar Takyar
Get Data from Microsoft Word Forms into Excel Using VBA
 
23:48
How to automatically get data from Microsoft Word forms into Excel using VBA. Get all the details here: http://www.exceltrainingvideos.com/get-data-from-ms-word-forms-automatically-into-excel/
Views: 53221 Dinesh Kumar Takyar
How to Automate Display of Pictures Using Comments with VBA
 
07:17
We can automate the display of pictures using comments with VBA in Microsoft Excel. The display of pictures automatically can be useful in a variety of situations. The code is given below: Sub showPic() For Each cell In Selection MyPicture = "C:\Users\takyar\Pictures\PillowCases\" & cell.Value & ".jpg" With cell.AddComment .Shape.Fill.UserPicture MyPicture End With Next cell End Sub Details at this link: http://www.exceltrainingvideos.com/how-to-automate-display-of-pictures-using-comments-with-vba/
Views: 4136 Dinesh Kumar Takyar
Follow specific link on web page automatically with vba
 
10:41
How to follow a specific link on a web page automatically with VBA. Details: http://www.exceltrainingvideos.com/how-to-follow-link-on-web-page-automatically-with-vba/
Views: 4180 Dinesh Kumar Takyar
Format dates with VBA
 
07:28
A number of users have found the formatting of dates in Excel difficult. We can automate the format of dates with VBA quickly and easily in Excel.
Views: 15195 Dinesh Kumar Takyar
Create and manage database in Excel with userform
 
18:39
How to create and manage a database in Excel with a user-form. Details available at this link: http://www.exceltrainingvideos.com/create-manage-database-in-excel-with-user-form/
Views: 40139 Dinesh Kumar Takyar
Highlight specific values using Excel VBA
 
11:49
We can color highlight specific values in our Excel worksheet data using VBA to make better decisions. In our example, a user wishes to make investments in banks in India that that could fetch him a minimum of 20% yearly returns. Of course, he has studied the balance sheet, cash flows, profit and loss, Earnings per Share growth and Price to Earnings per Share statements of the relevant banks over a period of 5 years. Details available at this web link: http://www.exceltrainingvideos.com/color-highlight-data-using-excel-vba/
Views: 10572 Dinesh Kumar Takyar
Extract Data from Sheet1 to Sheet2 based on Date Criteria with VBA
 
10:36
How to extract data from sheet1 to sheet2 based on date criteria with VBA. Details available at this link: http://www.exceltrainingvideos.com/extract-data-from-sheet1-to-sheet2-based-on-date-criteria/
Views: 29225 Dinesh Kumar Takyar
Web Scraping Data using ClassNames with VBA
 
18:20
We can use the class attribute to get data from a web page with VBA. In this video we scrape data from the following website: http://doctor.webmd.com/find-a-doctor/specialty/dermatology/alabama/gurley More details available at this link: http://www.exceltrainingvideos.com/how-to-scrape-web-data-using-class-names-with-vba/
Views: 17718 Dinesh Kumar Takyar
Looping Process to Automatically Check whether all Textboxes ComboBoxes ListBoxes are Completed
 
07:36
In this video we learn how to automatically check whether are all the fields in a user-form have been completed before transferring the data to an Excel worksheet using a 'for next' loop. The process is, of course, more efficient in code and execution than the VBA or macro code described in the last video to achieve the same goal. More details and a sample file available at this link: http://www.exceltrainingvideos.com/looping-process-to-automatically-check-whether-all-textboxes-comboboxes-listboxes-are-completed-before-sending-data-to-excel-worksheet/
Views: 6437 Dinesh Kumar Takyar
Ensure All Data Fields in UserForm Filled Before Transfer to Excel Worksheet Using VBA
 
10:22
How to ensure that all data fields in an Excel user-form have been filled before transferring the data to an Excel worksheet using VBA. All details and sample file available at: http://www.exceltrainingvideos.com/ensure-all-data-fields-in-userform-filled-before-transfer-to-excel-worksheet-using-vba/
Views: 24873 Dinesh Kumar Takyar
How to enter numerical values in all text boxes using class module in Excel
 
08:47
Most people shy away from learning about class modules which offer a powerful tool in the hands of an Excel user. How to enter numerical values in all text boxes using a class module in Excel is an attempt to make understanding of class modules easy. Details and sample Excel file for practice available at this link: http://www.exceltrainingvideos.com/enter-numerical-data-in-text-boxes-in-userform-using-class-module/
Views: 3080 Dinesh Kumar Takyar
Automate Email Extraction from Website Using Excel VBA
 
06:16
How to extract email addresses from a website automatically using Excel VBA. Sample file and details available here: http://www.exceltrainingvideos.com/automate-email-extraction-from-website-using-excel-vba/
Views: 8522 Dinesh Kumar Takyar
Scraping web page data of options in select object with vba
 
12:16
How to scrape web page data of options in a drop down list in select object with VBA. We learn interesting things like determining the number of values in a drop down list, getting the data values into our worksheet and finally selecting the option that we wish to activate. Complete details available at this web link: http://www.exceltrainingvideos.com/scraping-web-page-data-of-drop-down-list-options-with-vba/
Views: 10165 Dinesh Kumar Takyar
How to follow multiple links and extract data from webpage with VBA
 
11:18
The following steps are accomplished with VBA: First start the Internet Explorer. Ensure the webpage has downloaded completely through error handling. Look for all the links in a webpage, compare with the link you want, click on it. Come to the destination page. We have also used a timer to navigate to a webpage. Find the start position for data extraction. Define the end position for data extraction. Using a text function, we get the exact data we want. Details available at: http://www.exceltrainingvideos.com/how-to-follow-multiple-hyperlinks-and-extract-webpage-data/
Views: 24587 Dinesh Kumar Takyar
Search Display Print Archive data from Excel worksheet with VBA
 
14:35
How to automate search, display, print and archive data from Excel worksheet using VBA. Complete details available here: http://www.exceltrainingvideos.com/automate-search-display-print-archive-data-with-excel-vba/
Views: 5927 Dinesh Kumar Takyar
Add data from user form at top row below headers with vba
 
07:30
How to add data from user form to the top row below the headers in an Excel worksheet quickly and easily with VBA. Details available at this web link: http://www.exceltrainingvideos.com/add-data-from-user-form-at-top-row-below-headers-with-excel-vba/
Views: 4897 Dinesh Kumar Takyar
Transfer data from sheet1 to specific sheet using Excel VBA
 
08:25
How to transfer data to specific Excel worksheet using VBA. Details and practice Excel file available here: http://www.exceltrainingvideos.com/transfer-data-to-specific-excel-worksheet-using-vba/
Views: 8093 Dinesh Kumar Takyar
Automate Mail merge using Excel VBA
 
28:51
How to automate mail merge using Excel VBA without MS Word. Complete details available here: http://www.exceltrainingvideos.com/automate-mail-merge-using-excel-vba/
Views: 29004 Dinesh Kumar Takyar
Counting and Displaying Totals Automatically using Excel VBA
 
16:51
We can count and display totals of items automatically using Excel VBA. Complete VBA code, sample file and alternate solution available here: http://www.exceltrainingvideos.com/counting-and-displaying-totals-automatically-using-excel-vba/
Views: 8842 Dinesh Kumar Takyar
Transfer data from invoice to worksheet automatically with vba
 
13:16
How to transfer data from invoice to worksheet automatically with vba. Earlier we had learnt how to transfer data fro a worksheet into an invoice template: https://youtu.be/KFmXBgPVJw8 Get the details here: http://www.exceltrainingvideos.com/transfer-data-from-invoice-to-worksheet-with-vba/
Views: 41579 Dinesh Kumar Takyar
Check if folder exists with VBA
 
11:15
How to check if a folder exists with VBA. We can use both the DIR function or the File System Object. Let's also see the main difference in using both the approaches. Details available at my website: http://www.exceltrainingvideos.com/check-if-folder-exists-with-vba/
Views: 3032 Dinesh Kumar Takyar
How to Place Picture of Excel Worksheet Range into User Form with VBA
 
07:50
If you wish to place a picture of an Excel worksheet range into a user-form, you will first need to place the picture in a chart area, export the chart object to a GIF or JPG file and then place the image into the user-form. To create the picture of an Excel worksheet range we can use a simple copy-paste procedure or use the Excel camera to take a snapshot quickly. Details available at this web link: http://www.exceltrainingvideos.com/place-picture-excel-worksheet-range-into-user-form-with-vba/
Views: 13273 Dinesh Kumar Takyar
How to delete multiple blank rows in Excel worksheet using reverse for next loop
 
05:47
We can delete multiple blank rows between Excel worksheet data only by using a reverse 'for next' looping' process. The normal 'for next' loop doesn't solve the problem as it jumps to the next row with data without deleting a row as demonstrated by using a message. Get the complete details here: http://www.exceltrainingvideos.com/delete-blank-rows-from-excel-worksheet-using-reverse-for-next-loop/
Views: 3189 Dinesh Kumar Takyar
Capture user name,  login and logout times and calculate hours worked
 
15:32
We can capture the user-name, login and logout times and then calculate the number of hours worked automatically using Excel VBA. We need 3 files: 1. The file in which we wish to work 2. Log-details file 3. User-name and password file against which we can confirm the correct user-name and password entry of the person logging in. When we open our workbook a login form is presented. After confirming our credentials we can start working. In the process our user-name and login time is recorded. We ensure that the user cannot close the workbook without logging in. This is done by disabling the cancel button 'X' on the right corner of the user-form using an IF statement. Most of the details have already been discussed in earlier videos. A few links are given below: 1. http://www.exceltrainingvideos.com/how-to-get-multiple-values-from-closed-workbook-using-vba/ 2. https://youtu.be/XnKRHmgDn84 - find next blank row 3. http://www.exceltrainingvideos.com/copy-worksheet-from-another-workbook-rename/ - how to check whether a workbook is already open After finishing our work we close the file and the macro or VBA code automatically records the logout time. In the log-details file we run an interesting macro that calculates the amount of time we have worked.
Views: 39692 Dinesh Kumar Takyar
Automate Copying Column Data From Sheet1 to Sheet2 in Different Region with VBA
 
06:57
Automate Copying Column Data From Sheet1 to Sheet2 in Different Region with VBA. We have to be careful about the assignment of the column index number in the variable erow which finds the next blank row for data entry automatically.
Views: 5730 Dinesh Kumar Takyar
Automatically cut paste data rows from an Excel worksheet into another based on date
 
07:51
How to automatically cut paste data rows from an Excel worksheet into another based on date criteria using VBA and then delete all the blank rows in the original worksheet also automatically. Get all the VBA details and a sample file here: http://www.exceltrainingvideos.com/automatically-cut-paste-data-rows-from-an-excel-worksheet-into-another/
Views: 16092 Dinesh Kumar Takyar
How to run macro from another workbook with VBA
 
04:02
We can run a macro from another workbook with VBA quickly and easily. That is, if we know the path to the macro, we can define it in a macro using specific syntax and access it. In similar manner, we can also access data in other Excel files. More details available here: http://www.exceltrainingvideos.com/how-to-run-macro-from-another-workbook/
Views: 28253 Dinesh Kumar Takyar
Create report automatically with headers at specific positions using Excel VBA
 
18:35
How to create a report automatically with headers at specific positions using Excel VBA. Details with Excel VBA code and sample file: http://www.exceltrainingvideos.com/create-report-with-headers-at-specific-positions-using-excel-vba/
Views: 7214 Dinesh Kumar Takyar
How to Create Excel Data Form Quickly with VBA
 
05:59
We know how to create a data form using standard procedures: http://www.exceltrainingvideos.com/excel-data-entry-form/ http://www.familycomputerclub.com/excel/data-form-in-excel-2007.html How to create and display an Excel data form quickly with VBA without an in-depth knowledge of VBA coding. All details and a sample file available here: http://www.exceltrainingvideos.com/create-excel-data-form-quickly-with-vba/
Views: 14700 Dinesh Kumar Takyar
Automatically copy paste data from non adjacent cells in sheet1 to sheet2 with VBA
 
08:43
How to copy paste data from non adjacent or non contiguous cells in Sheet1 to Sheet2 using Excel VBA. Complete VBA code and sample file available here: http://www.exceltrainingvideos.com/automate-copy-paste-data-in-non-adjacent-cells-in-sheet1-to-sheet2-with-vba/
Views: 10324 Dinesh Kumar Takyar
How to copy data from one Excel Sheet to Another Automatically based on Time Criteria Using VBA
 
06:31
How to copy data from one Excel Sheet to Another Automatically based on Time Criteria Using VBA. Complete details available on website: http://www.exceltrainingvideos.com/copy-data-from-one-excel-sheet-to-another-using-time-criteria/
Views: 6092 Dinesh Kumar Takyar
Using timer to automate getting data from closed workbook
 
08:31
We can use a timer to automate getting data from a closed workbook at specific times quickly and easily. We also learn how start Excel automatically when we start our computer, open a specific Excel workbook and then get data automatically from a closed workbook at specific times. More details at this link: http://www.exceltrainingvideos.com/using-timer-to-automate-getting-data-from-closed-workbook/
Views: 4165 Dinesh Kumar Takyar