VBA Code that extracts multiple tabs at a time based on lookup tab

[SWahl]

I have a workbook with 70 tabs to be extracted, some individually but most in groupings. Ultimately, I need to send each senior director a workbook with a tab for each of their departments ("Cost Centers"). Each tab name represents the cost center name.

For example, John Doe oversees Accounts Payable, Accounts Receivable, and Procurement. I need to extract those three tabs from my master file and copy them to a new file named "John Doe - 2025 AOP Budgeting".

I have a source/lookup tab named "Cost Centers" which lists all cost centers (which also represent each sheet name) in column C and the senior director in column D.

I am looking for a VBA code that will create a file for each senior director and their associated cost centers.

Hopefully this is clear enough, but let me know if more info is needed!


For extra credit, I would love a macro that would then draft an email to send each senior director a copy of the file, based on a template email. The corresponding emails would be listed in column E of the "Cost Centers" tab.

 

[Logit]

In order to give you the best answer/assistance possible I had requested the ability to view your workbook. Can you post a copy of your workbook to a share site, mark for sharing & post the link you are given here.

 

[SWahl]

Here is the link - 2025 AOP Planning - empty.xlsm

Confidential data has been extracted from the file but this should give you a better idea.

 

[SWahl]

In order to give you the best answer/assistance possible I had requested the ability to view your workbook. Can you post a copy of your workbook to a share site, mark for sharing & post the link you are given here.

Apologies, I commented yesterday but just realized it wasn't a direct reply to you -

Here is the link - 2025 AOP Planning - empty.xlsm

Confidential data has been extracted from the file but this should give you a better idea.

 

[Logit]

I've downloaded the workbook and working on a solution.

 

[Logit]

You can download the updated workbook here : Internxt Drive – Private & Secure Cloud Storage

NOTES:

For these new macros to work as you intend, you must not create MERGED CELLS on any sheet.
MERGED CELLS do make things look nice in a workbook but they create havoc on most all
programming attempts including these new macros.

Unless you edit the new macro code, the layouts you presently have established for your workbook
must remain the same.

To email the SR DIRECTOR sheets, you will need to create a folder on your desktop and name it
Emails. This is the temporary location for the new workbooks that will be emailed to each
Sr Director.

Prior to clicking the FILTER / COPY / PASTE button, be certain the various Sr Director sheets
no longer exist in your workbook. The macro that creates the individual Sr Director sheets
does not check for the sheets existence prior to creating new ones.

Clicking the DELETE SR DIRECTOR SHEETS will rid the workbook of all Sr Director sheets. Be certain
to click this button prior to clicking on the FILTER / COPY / PASTE button to prevent any
errors in creating new Sr Director sheets. If ;you want to maintain a copy of all Sr Director sheets
it will be necessary for you to manually copy the sheets and paste them in a different workbook.

The Email Addresses sheet should be self-explanatory. Be certain you maintain the same formatting
of columns A & B ... otherwise the emailing macro will not function correctly.

In the Sub MAILRUN, you can edit the text that will be displayed in your emails. The text in SUBJECT
and BODY are the only two lines you can edit. All other fields are automatically filled in.

The email macro is currently setup to display each email prior to sending. This provides you an
opportunity to review the email prior to sending. If you are comfortable auto-sending the
emails, comment out the DISPLAY line and add a line below it that says .Send . Include the
period immediately prior to the word SEND.

Presently (at least here) the various values created by the many formulas in use, do not show the intended
data. I trust you will be able to correct the short coming on your side. The FILTER / COPY / PASTE macro
copies the values only ... not formulas.

Play around with the workbook and see what you think. I believe I've created an updated workbook that
produces everything you were seeking.

When you go to editing the workbook I recommend you do so on a copy of this workbook. You can always
download a fresh copy but it is usually best to maintain an original, clean copy somewhere on your
hard drive that you can always make another copy of.