Kashif Javaid

In the course of one career, there may be more than one occasion where one need to pull some data from one or multiple excel spreadsheets into another main spreadsheet for summary or management update. This can typically be done by opening one file at a time and copying required information and pasting into the summary spreadsheet. Now this may be cool for one or few times, but if this task is required on daily basis, this could quickly become mind-numbing and outright boring. Luckily, excel has built in mechanism to automate these tasks: VBA.

In this application note, I will show you, this kind of task can be easily automated using excel visual basic using an example.

Lets assume you have a big excel file that comes from a vendor or some other group in your company (a sample data file link is given at the end). This excel file gets updated daily and send out to you. Your task is to extract some particular regional data only and need to send it upper management periodically.

Here are the steps:

1) First load the visual basic editor as shown below:

2) Assume, there is a sale file where sale information is updated regularly for different Regions (see snapshot below). The problem is that it is not organized, and your task is to extract information for your region. Lets say it’s “West” and you need to send it to management daily. You could do it manually possibly filtering rows but task is boring and takes extra steps.

3) Insert a button into the active sheet with J1 cell as Region input we want. Shown below is what happened after user clicked on the Get Data button. Note that you need to have developer tab enabled to see the button and other GUI controls. Refer to Excel help to see how to enable it if its not already there.4) The main program is extremely simple and consist of four subroutines:Initialize, OpenFile, GetData and CloseFile. There are two variables, app and CustFile. Basically program create a new excel workbook customer file with the same data, but it does not change the source file5) OpenFile and CloseFile, basically consist of code that open the file and create a new workbook. The beauty is that it does that automatically and do not care about the source file name. The only requirement is that data file need to be in the same folder.

6) GetData is where actual data is imported into summary workbook. This sub basically go over all the occupied rows and copy the data for each Region and OrderDate, TotalCost for each row into variables Region, OrderDate and TotalCost. RegionNeeded is used to store button input from cell J1. As you can see, this can become very powerful if you need to customize or have some special requirements7) Moreover, I need to mentioned a helper function called fcol. Basically fcol function scan the first three row and up to thirty columns to get the column number based on the column header as specified by input argument of this function8) Now there you have it, it’s a very simple program but could prove powerful and real time saver and can be adaptable to your particular requirements. I have attached both files if you would like to play with them. Modify it to make it work with other columns. Here are the downloads links: summary and SampleData.

9) Create a new folder, save both file there, open summary workbook and hit command button. Type different region such as ‘Central’ or ‘East’ and watch how the rows updated.

10) In conclusion, with some basic programming constructs such as for loop, and some if and else statements, a very powerful and useful excel automation can be created.