How To Use Excel VBA Code Real Example P9
How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the 'click-of-a-button', saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances - a challenge indeed! Let's get into it...
Download File Link:
https://tinyurl.com/y4d6ds62
----------------------------------------------------------------
In part 9 of the real-world VBA series, we tackle the next part of the briefing from Eric, our ‘client’ for this project. We have to list the customers in four groups, on four different sheets, according to how many times the customer appears in the file. For example, all customers that appear on two sheets should be listed together.
This involves creating new sheets to store this information. Chris talks about the value of concise sheet names; they are helpful because shorter sheet names mean more tabs visible at the bottom of the Excel window, and more efficient navigation through the file. With this in mind, we shorten the existing sheet names, and create new sheets with concise names and a consistent naming convention – that will help us with the coding later.
Here’s a challenge: can you spot the problem the new sheet names create with the analysis we created using the INDIRECT formula, earlier in the series? Not covered in the video!
With the new sheets created, named and re-ordered, it’s time to think about how to get the right names on the right sheets! As usual, we take a step back and try to get the concept, before starting the coding.
Our idea is to loop through the names on the Analysis sheet once again, and then to move the name to the correct sheet according to the value in the ‘total’ column integrated previously in the series – we told you it would come in useful later!
With the concept clear, we set about creating the required code. We ‘concatenate’ the value in the ‘total’ column for the customer with the letter ‘L’ to give us the sheet name, and then store the name in a variable. Now, it is a small step to transfer the data to the correct sheet.
The video is completed with a small-scale test to prove that the code positions the names on the correct sheets. In the next video, we will look at how to ‘order’ or ‘stack up’ the names to create the lists specified Eric’s brief. We probably won’t use any new coding – so how about trying to do that step yourself, before Chris’s video demonstration is released? Let us know how you get on in the YouTube comments!
See you in part 10.
----------------------------------------------------------
Series Playlist Link:
https://tinyurl.com/y4pmrkxn
🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know
https://tinyurl.com/yc7jlmbs
/ tigerspreadsheetsolutions
/ tigspreadsheets
http://tigerspreadsheetsolutions.co.uk
Also on Insta :-)
Watch video How To Use Excel VBA Code Real Example P9 online without registration, duration hours minute second in high quality. This video was added by user Tiger Spreadsheet Solutions 09 August 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,993 once and liked it 34 people.