Macro Intro
This macro is the combination of 3 Excel macros: Create Worksheet per Filter Value | VBA Macro #16, Save Worksheets as Workbooks | VBA Macro #18, and Create Emails with Workbooks Attached | VBA Macro #23.This Excel macro starts by filtering a column and create a new worksheet for each unique value found. By default, the active column selected will be filtered. If the active column selected is blank an InputBox will ask for the column number to filter. (Example: A=1, B=2, C=3, etc.) Once a column has been identified the duplicate values will be removed and what remains will be used to determine how many worksheets to create. Each worksheet will contain the filtered information based on each value found. Next each worksheet will be saved as their own workbook. By default, the Folder Path of the Active Workbook will be used so the new Workbooks are saved at the same location. (Customize: You can indicate a different path in the VBA code) When saving the Workbook name will be the name of the respective Worksheet. Finally, this macro will use the name of each Worksheet to find Workbooks with the same name in the Folder Path. If a match is found a new email with be generated with the Workbook attached. The Outlook email generated will have the subject and body populated. With a few updates to the VBA code you can have specific recipients pull in based on the Worksheet name.
These macros used together automates an entire process! The time saved using this macro allows you to focus on how you can bring more value to your customers. Take a look at the Value Stream Map we discussed in the Macro Monday video. We don’t know the source of the initial data but what follows is a standardized process leveraging VBA to complete all this busy work. Creating processes like this don’t just save time but also mistake proof processes eliminating defects. You won’t send the wrong attachment to the incorrect recipients if the computer performs the action for you. Of course, there is the potential the code doesn’t work but you can spend time ensuring this macro always works instead of relying on your ability to complete the steps of this process perfectly each and every time.
See it in Action
Watch this video to see this macro in action.Code
Here is the code for this macro. Make sure the following References are setup before running it: Visual Basic For Applications, Microsoft Excel 16.0 Object Library
Customization
These segments of code can be customized to personalize this macro.‘Active Workbook Folder Path By Default the Folder Path is the Active Workbook but you can change this to a different path if desired
If the filtered column returns more than 50 values the macro will exit to ensure the Excel can handle the request. Update this number to reflect how you want this to behave.
If you are not working with a header row then you should change Counter = 1
If you don’t want the filter to be removed from the first spreadsheet make this line a comment with ‘
If you don’t want the duplicates column to be deleted from the first spreadsheet make this line a comment with ‘
Remove the MsgBox or make it comment to not see it when this macro runs
Remove the MsgBox or make it comment to not see it when this macro runs
By Default this macro will start at the 2nd Worksheet and loop through all the Worksheets following it. You can update 2 to a 1 to include the 1st Worksheet.
Update the Sheet Names and set the Recipients (also CCRecipients) with the appropriate email addresses
If CC’ing Recipients then update this code line to not be a comment
This code line controls what populates in the Email Subject
These code lines control what will populate into the Email Body
If you want the email to send automatically then update this code line to not be a comment
Remove the MsgBox or make it comment to not see it when this macro runs
First time using VBA?
The Developer Tab is an additional section of the ribbon when activated allows you access to Visual Basic in Applications like Access, Excel, Outlook, PowerPoint, Word.
Setup Now
Setup Now
Once the Developer Tab is available you will have the capability to update your security to allow Macros to run in the current application.
Setup Now
Setup Now
A sub of code is a collection of objects and variables. For the code to successfully run a library of references needs to be set.
Setup Now
Setup Now
Subscribe as an Insider to receive additional rights.
If you like our content and want to show your support tip us here!
Contact Us
Looking to improve your computer processes?
Leverage & Lean is here to help!
Leverage & Lean
Is focused on improving computer processes by Leveraging programming and applying Lean principles. It is our goal to make you more efficient and effective on your computer producing “Less Clicks and More Results!”
