Lesson 4: Project 2
This lesson will tackle a more involved example involving multiple worksheets.
Introduction
In this lesson we are going to look at a problem involving the refinement of data, that is, taking a large amount of data and removing the bits we don't want, leaving what we do. This process will involve the matching of values in two sheets to thin out the data.
This will illustrate nicely the process of working with multiple sheets. At the end of the process we will create a Word table from the data. This last aspect will show you a little of the inter-application capabilities of macros.
PDF Version of Lesson 4The Problem
Look at the spreadsheet Lesson 4.xls, it contains two worksheets - {Projects} and {Bdgt}; there is a backup for the Bdgt worksheet in Lesson 4 Backup.xls. I will explain shortly why I did not include the backup sheet in the main spreadsheet.
Okay, so here is the problem to be solved. The {Projects} sheet contains a list of projects that I need the actuals for from the budget sheet {Bdgt}. Even though there are many more projects in the {Bdgt} sheet, I'm only interested in a few of them. I created this list of projects manually and I also copied the budget data into the {Bdgt} manually because it was just not worth creating macro code to do this.
In the next course we will do this type of activity as there are many more sheets to deal with. Always try to evaluate the effort to the worth of your macro code.So, starting with these two sheets, I first need to strip out unwanted rows from the {Bdgt} sheet - the criteria are any blank or Total lines. I then remove unwanted columns. The ones I don't want to keep are columns B, E, G-I, M, and N. I've highlighted these in red. Once this is done, I then create a new sheet called Actuals, and based upon the projects listed in the {Projects} sheet, I copy the corresponding rows for that project from the {Bdgt} sheet to the {Actuals} sheet.
Few! Well, what does all that mean? I'm just copying the actual data for only the projects I'm interested into the Actuals sheet.
If that wasn't enough, I now create yet another sheet, called Totals, and then sum the actuals values within SR and Appl Id. Why do you do this? I hear you ask, I don't know, they just make me do it! Actually, it's an accounting thing, keeping track of projects and their cost.
Just when you thought it was over, we then copy the resulting Totals data, and create a table in a Word document. This last little bit of code will show you how to interact with other Microsoft ® applications.
I know that this seems a lot to do, but I will explain the code as we go through it and you can always play with the code snippets in your own macros - that is how I learn. There is an amazing amount of code, freely available from books and on the Internet. The texts for this course list a number of references, some of which I have listed in the course details.
