VBA Loop(?) that Copies a Tab and Find/Replace Text String

[kirklj]

Greeting!

I am in need of some help with constructing a macro. I've built a workbook containing a table similar to the following:

Company Name	Directory Path
Company B	Path B
Company C	Path C
Company D	Path D
Company E	Path E
Company F	Path F
Company G	Path G

And among several other tabs in the workbook, there is a template tab named “Company A” that will need to be copied. Also, the data link in the sheet are specific to Company A. That said, the macro should be able to perform the following:

1. Copy template tab "Company A", and then place the copied tab after the "Company A” tab.
2. Rename the copied tab to "Company B" and then Find/Replace Directory Path A with Path B.
3. Repeat step 1 but place the copied tab after the "Company B” tab. **
4. Repeat step 2, but with the “Company C” tab name and directory path.
5. Continue to loop through the Company Name - Directory Path table indefinitely until each company is represented with its own tab. (This table will continue to grow.)

**NOTE: For each subsequent copy of template tab "Company A", the copied tab will need to go to the "back of the line" so that when the macro concludes, the tab order is Company A followed by Company B followed by Company C, etc.

Thanks in advance for your help! It is greatly appreciated…..

 

[bebo021999]

COuld you post image of sheet A with link displays?