Modify the Contents/Formats of Existing Workbooks

Many times we face the need to modify our data (in this case, physician records) in some way AFTER we have already bursted the data into territory-level workbooks. Instead of modifying the national data and re-run data bursting, we can also choose to modify each territory file with physician data already populated.

Assume that we would like to make the following changes to our already-populated 99 workbooks:

1), Make all last names upper-case

2), Insert a new column between Columns A and B, name it "Region #", and fill in the regional # of each physician

3), If the physician level is "RxDx" AND the physician segment is "None", change the segment from "None" to "Low".

Without further delay, here is the code:

Some tricks that are worth mentioning:

1), Application.ScreenUpdating = False stops your computer from refreshing the display during the Macro run. There are two advantages of doing this. One is to increase the speed of Macro execution. Two is to avoid all the flickering on the computer monitor (can be very annoying). I highly recommend adding this line to all your VBA codes which cause repetitive display refreshing.

2), .Range("B:B").Insert method inserts a new column to the left (shift:=xlToRight) or to the right (shift:=xlToLeft) of the existing column B. Note that shift parameter determines the shift direction of the existing column B, not the new column to be inserted. To insert a new row, use .Range("2:2").Insert shift:=xlDown or .Range("2:2").Insert shift:=xlUp.

3), When comparing two text strings, first convert both into uppercase strings. This would eliminate any unintentional errors caused by different upper- or lower-case combinations. Note that VBA will treat two text strings as different as long as there is upper- or lower-case difference, even when all the letters are the same. For example, "rxdx" is different from "RxDx" or "RXDX".

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.