Friday, October 22, 2010

How to prepare the master schedule for the macro run

Before you run the main parsing macro on a new master schedule, you need to set up a lookup table with Excel sheet ids in one column and the corresponding rulemaking id in another.
  1. Create a new tab at the front of the workbook called "Rulemakings."
  2. Run the macro called PrintWorkSheetNames(). This will populate the Rulemakings tab with a list of all the rulemakings in that workbook and their corresponding Excel sheet indexes.
  3. Use MySQL Workbench (or some tool) to copy the production version of the rulemakings table into the Rulemakings tab. Leave an empty column between the first set of columns (created by the macro) and the second set of columns (which you just pasted in) so that each group can be sorted independently.
  4. Sort each group by the rulemaking abbreviation. These SHOULD be unique, and they should be the same.
  5. When you are sure that each row lines up properly as a match all the way across, delete the empty separating row (thus locking them together for the sort).
  6. Make the "sheet index" the first column and the "rulemaking_id" the second column.
  7. Sort it by sheet index. This is so that vlookup() works properly.
  8. Highlight the whole range of those two columns and name the range "rulemakings." This is used by the product_tabs_to_dates() macro.
With those steps complete, the workbook is now ready to run the product_tabs_to_dates() macro.

No comments:

Post a Comment