How to Burst Physician Data into 99 Territory-level Workbooks

Following the last post, we now have 99 (empty) territory-level workbooks for our physician call panel lists. The next step is to write a Macro to populate these workbooks. Before we can do this, we need a sheet containing the physician list for the entire nation. We will add a new tab to our now familiar "Mikoudai Inc Sales Hierarchy.xls", and name the tab "Nat Data". Within this tab is our physician data for the entire nation. You can download the file here. It may take a while to download since it contains 15,707 physician records. All physician data are fictional.

With the physician data ready, add the following code into the same "Mikoudai Inc Sales Hierarchy.xls" file that you just downloaded (see link above).

The code is slightly more complicated than what we've seen in the last post, but the underlying thinking is simple: each time we open a territory-level workbook, we loop through the entire national physician list line by line. If the territory # matches (If data_sheet.Cells(m, 2) = ter_list.Cells(i, 5) Then), the single-line physician record is copy-pasted into the corresponding territory-level file. All 15,707 lines of records will be "scanned" in this way before we save the territory-level workbook, close it, and make ready for the next territory file. Such process will repeat 99 times all together. Although this may sound a lot of data processing, on my laptop running Excel 2007 the entire Macro takes less than 5 minutes to run.

Some explanations for certain methods/functions we used in the above code will help the understanding:

.Range(cell).End(xlUp).Row: returns the row number (in Integer) of the last non-blank cell in the same column above the cell defined in "Range()" object. In reality, if you use a cell that is way down the bottom of the sheet as the reference (in our case, we used Cell "A60000" as we knew there was no way we would have close to 60,000 physician records), .Range(cell).End(xlUp).Row returns the row number of the last physician record, period. The advantage of using such code is that if we need to add more physician records later on, we do not have to worry about updating our codes.

Selection.PasteSpecial xlPasteValues: paste values only to the destination cells. Other commonly used PasteSpecial methods include paste formats only (xlPasteFormats), paste formulas only (xlPasteFormulas), and paste all (xlPasteAll), which could also be achieved by simply using ActiveSheet.Paste.