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".