Massive project involving Word & Excel

[JohnnyBravo]

I'm sending out an RFI to 100 hospitals across the country. The RFI's relate to 14 different transplant types - here are a few examples:

-- Adult Heart
-- Adult Lung
-- Adult Liver
-- Pediatric Autologous
-- Pediatric Allogeneic

There's an excel workbook where we send to the hospitals and there's a tab for each of these transplant types. We'll call it Solids.xls. So before I send it, I have to delete the sheets that do not pertain to that hospital.

Example:

ABC Hospital addressed to Mr. Jones... blah blah blah..

At the end of the first paragraph, the transplant type is specified. All the information EXCEPT for the transplant type is stored in an Access db so merging the records hasn't been a problem. The inefficient part comes in where i have been (upto now) manually typing in the transplant type for each hospital.

New Idea: A friend suggested that I take my db of contacts and export it to Excel - which is a good idea. Then insert a separate column (name it as: "organ" or "transplant type" or whatever.) Now I can merge the hospital name, contact name, AND the TRANSPlANT type at the same type. Good deal. Here is where it gets more involved.

Remember that Excel workbook I mentioned earlier? Well the generic one contains sheets for every transplant type - all 14 of them. There are tabs w/ the names of the organ (or transplant type) so I simply open up the workbook, delete the one(s) that do not pertain to that hospital.

Question: In the worksheet (previously exported from the Access db), for the column heading of "transplant type", I would like to type numbers in place of organ. By assigning a number for each organ (transplant type), it saves time. In other words, If ABC Hospital specializes in Adult Heart, Adult Lung, Adult Liver, I want to type in 1, 3, 5.

How can I get VBA to (after I type in the number code) to find and replace the numerical assignment and for the actual organ. And do it for only the column specified.

How can I get VBA to pull up the "Solids.xls" file then delete the sheets that do that pertain to the hospital?

Everything has been done manually thus far - it is absolutely tedious and error prone - I don't like it one bit. There's has GOT to be a better way - no?

[russkie]

Sounds not too Difficult, But it would be a ton easier to help you if you could inclue the excel file you want to do all this to, fer specifying Sheets and columns with which information....

[JohnnyBravo]

Sounds not too Difficult, But it would be a ton easier to help you if you could inclue the excel file you want to do all this to, fer specifying Sheets and columns with which information....

Do you mean you want me to e-mail you the Excel workbook??

Nevermind, I just figured out that you meant that you want me to zip it up and upload it to the forum server. Give me a sec...i'll be back.

OK - done. I've zipped the excel file.

[JohnnyBravo]

I would like one of the moderators or the site admin to remove the Excel file which I attached to my previous posting. Is it possible?

I realized that after I posted, there's too much confidential information in the file. And no one has come back to answer the thread so there is no point in having it available for everyone to download.

[Zack Barresse]

I would like one of the moderators or the site admin to remove the Excel file which I attached to my previous posting. Is it possible?

Done.

[russkie]

ehm.. sorry johnny, was end of my work which is when i check the posts. thats why no reply for long time. Maybe you can recreate a mini-version of the file u actually want to make with made up information? Then no confidential stuff released, and i would then be able to put my first 'helping' post instead of need help post