How To Create, Open, Save and Close 99 Files For 99 Territories From One Template File

One common task for Biotech managers is to create a "call panel" workbook for each territory, containing a list of physicians within that territory whom the rep is required to call on during the quarter. As that essentially means that we need 99 workbooks as our final output (one for each territory), it is smart to begin with one template file, having all the rows and columns and formatting that we desire. We call it "Call Panel Template 2011Q1.xls". It's just a template so no real physician data is added at this time. Download the template file here.

Next step for us is to use VBA method "SaveAs" to save the template file into 99 territory-level workbooks. Let's assume that for such territory-level workbooks we would like to have both territory # and name as part of the file name, for example, "Call Panel 2011Q1 - 10101 Seattle.xls".

Open our "Mikoudai Inc Sales Hierarchy.xls" workbook (remember? We use this file as our "Macro" file), and enter the following VBA code:

The above code does several things. First of all it opens the template file - Call Panel Template 2011Q1.xls. It then sets up a loop in which the template is saved as a new workbook with corresponding territory # (ter_list.Cells(i, 5)) as well as territory name (ter_list.Cells(i, 6)). Finally it closes the template after all the workbooks have been created. Please note that for the code to work properly in Excel 2007-2010, "FileFormat" property of "SaveAs" method must be given. See the table below for main file formats in Excel 2007-2010:

51: .xlsx

52: .xlsm

50: .xlsb

56: .xls

Make sure to use both the correct file type and FileFormat number in your own code.

Now that we have created all the workbooks. They are all identical at this point. Next we would like to modify each workbook so that:

1), the name of the single tab in the workbook is changed from "Template" to the corresponding territory name;

2), territory # is added to Cell F1.

The code to do this is the following:

This is just a sample of what you can do to modify each individual workbook. I hope you find the code straightforward and easy to understand. You can download the codes shown in this post here.