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.

 

[SWahl]

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.

Hello! Thanks so much for your help on this. I am having trouble running the macro successfully, can you confirm what the intended order of these macros are? Do I click "Filter/Copy/Paste", then "Email Sr Director Sheets", then "Delete Sr Director Sheets?"

To confirm, the macro is intending to copy all sheets to a new workbook for each director, correct? For example, Sr Director 1 would have one workbook with 6 sheets in there representing each cost center.

 

[Logit]

All of your questions are correct : the order in which you click on the command buttons is Filter/Copy/Paste first ...
That macro will copy all the appropriate rows to the respective Sr. Director sheets. After clicking this button you can
move to the last sheet in your workbook and see all of the sheets that were created for each director. Look t the
contents of the new sheets.

Clicking on Email creates an email for each director with their respective sheet attached as a new workbook. Initially
the new workbook with the director's sheet inside is temporarily placed in the new folder on your desktop. The folder
is named Emails. As each email is create, the new workbook is deleted from the Emails folder.

The Delete button rids your master workbook of the new Director sheets that were created initially. Clicking the Delete
button prepares your master workbook for the next session of emails.

You can double click each workbook attached to the emails to view the contents. You should see all of the data pertaining
to each director.

 

[SWahl]

All of your questions are correct : the order in which you click on the command buttons is Filter/Copy/Paste first ...
That macro will copy all the appropriate rows to the respective Sr. Director sheets. After clicking this button you can
move to the last sheet in your workbook and see all of the sheets that were created for each director. Look t the
contents of the new sheets.

Clicking on Email creates an email for each director with their respective sheet attached as a new workbook. Initially
the new workbook with the director's sheet inside is temporarily placed in the new folder on your desktop. The folder
is named Emails. As each email is create, the new workbook is deleted from the Emails folder.

The Delete button rids your master workbook of the new Director sheets that were created initially. Clicking the Delete
button prepares your master workbook for the next session of emails.

You can double click each workbook attached to the emails to view the contents. You should see all of the data pertaining
to each director.

Hmm I think there may be some confusion. I am not trying to create new sheets for the directors, simply just want to copy each director's associated sheets to a new workbook.

 

[Logit]

simply just want to copy each director's associated sheets to a new workbook.

The macro does this. You also stated you hoped to be able to email those sheets (compiled into a new workbook for each director) to each director. The macro does that as well.

I am confused what you are saying. From here it sounds like exactly what the macro does. If the macro isn't performing as you desire, please try to explain your goal in a different
manner and perhaps I can grasp what you are shooting for.