Create new workbook from two existing worksheets from list of names

[Groovicles]

Hi everyone,

I have a workbook with multiple worksheets. Worksheet "List) contains a list of worksheet names, all of which are found in the workbook. These worksheet names are listed from B2:B20 and C2:C20.

What I'm trying to do is build a macro which will run through each row between B2 and C20 and copy the two worksheets in the row to a new workbook. For example

B2: WS1a
C2: WS1b
B3: WS2a
C3: WS2b

So the macro would run through rows 2 - 20, find the worksheets listed in columns B and C, and copy them into a new workbook. It would loop through the entire list until 20 workbooks are created, each having the 2 proper worksheets in them. The workbooks would be named based on the B column value of the two worksheets.

Sort of complicated I know. A bit stumped on this one.

Any ideas?

Thanks!!

[AlphaFrog]

Please Login or Register  to view this content.

[Groovicles]

Hi Alpha,

Thanks very much, that works great. In Cell B32 I have the exact file location I'd need to get these new workbooks saved. How can I add that into your code?

Thanks!!

[Groovicles]

Ah, nevermind. I got it. Thanks very much, Alpha!!

[AlphaFrog]

Your welcome. Thanks for the feedback.

[Groovicles]

Hi Alpha,

Very sorry, another question for you. Before each workbook is saved (or after, doesn't matter I guess), I need to have it emailed to a recipient and cc'ed to another recipient. The email TO email addresses are in range ("D2:D25") and the cc addresses are in range ("E2:E25"). Header for each email is the same as the file name, and email text is located in Cell B31. I have some experience in creating emails and attaching, but nothing like this.

Any ideas?

Thanks!

[AlphaFrog]

This site has methods and example code to email from Excel.

Mail from Excel with Outlook

[Groovicles]

Hey Alpha,

Thanks again for the awesome coding help. I'm trying to have all external connections delete in each new workbook created and I'm having some trouble. I'm not getting any errors, but the connections are not deleting either. Here's what I have:

Please Login or Register  to view this content.

[AlphaFrog]

Correct me if I'm wrong, but I'm guessing you want to replace formulas that have links to external sources. If yes, this will replace all formulas (and any external links) with their values.

Please Login or Register  to view this content.

[Groovicles]

Hi Alpha,

I was hoping the internal formulas would remain, just the formulas was with external links to be removed?

Thanks!

[AlphaFrog]

Please Login or Register  to view this content.

[Groovicles]

Absolutely perfect. I was trying the method you used without success but I can see were I had it wrong as well. Thank you very much for your help.