EXTRACTING ROWS OF DATA/FORMULAS TO CREATE NEW WORKBOOK

[JingleRock]

EXCEL 2003

I have just created a six-sheet macro file/spreadsheet to:
1) download Bloomberg API Data for 600 securities (20 data fields);
2) run AdvancedFilter to isolate certain changes in data from
preceding day;
3) create a HTML string that is pasted into the body of an email;
4) save macro file; and
5) email report to specified parties.

Now, I have been told that I have to divide my list of securities into
three groups and have the macro send out an email report for each
group. Security membership in each of the groups is color-coded in
the Name field.

The Bloomberg data is downloaded to my 'BB DATA' Sheet; my 'WORKPLACE'
Sheet contains about 12,000 formulas, nearly all of which have a
reference to 'BB DATA'. (The BDP() formulas in my 'BB DATA' Sheet are
not problematic to copy to a "new" workbook.) How do I copy these
formulas and have them refer to the "new" 'BB DATA' in a "new"
workbook?

Security list is in 'BB DATA' and in 'WORKPLACE' Sheets; both are in
alphabetic order on the Name field.

I want to develop a macro to help me convert what I have to a 3-
workbook
situation. Do you have any tips or any references to sample code?

 

[Patrick Molloy]

it sounds like over-kill to split the workbook
why not just add a colunm to say which section each security is in then loop
through your code for each section, parsing the securities for that section?