Hello,
I'm hoping someone could help me out with a file I have at work that is taking lots of time. We get a report with multiple division's information on it, and we have to filter down for each division on each tab (removing the other divisions), and then email out the files to that respective division.
I've been digging around on the board, and I know it's possible to do what I'm trying to do via VBA, I'm just a little lost at where to begin.
Overview:
There are three data tabs in the file, Summary1, Proposal1, and Mgmt1. I get all of the data in these files and have to filter down to each division and delete the other division's information. Once I do that, I have to refresh a pivot table, save the file, and then email the file as an attachment to a pre-defined group.
Detail:
The file starts with a "Summary1" tab, and the division starts in cell A4, going down. There is a second tab in the workbook with the same division in cell D4, going down (sheet name is "Proposal1"). On the Proposal1 tab, there are a series of companies listed in column E. In order to filter the last tab, "Mgmt1", you need to lookup the remaining values in column E in the Proposal tab after you've filtered, and delete the "N/A's" (companies to lookup are in column A for the "Mgmt1" tab). The data is arranged on the Mgmt tab so you could do a vlookup in column I and then delete it after the lookup and filter has occurred.
Lastly, there is pivot table on the tab "Pivot", the Pivot table name is just "Pivottable1". This needs to be refreshed after the filters have occurred.
Once the data has been filtered, the file needs to be saved to a location, we'll use "C:/Sample/" for this example, with the division's name on it. The file then needs to be attached to an email and sent to the division (really just need to use the display versus send functionality so I can review the files before sending).
On the spreadsheet, I've created a tab named "Email_Details", and put the division names in column A, the "To" recipients in column B, the subject in column C, and the body in column D. If a division occurs in the Summary1, Proposal1 or Mgmt1 tabs that isn't on the email tab, I'd like an error message to pop up telling me to insert the data before resuming. If one of the divisions isn't on the first three tabs, but is in my email tab, that can be ignored. I'd also need to hide the "Email_Details" tab, or better yet delete it.
I'd like the code to loop through each division, filter down the results (and delete the others), save the file and create the email.
Thanks so much in advance and sorry for the long post! I'm happy to share a file if easier to look at the information that way or if something isn't clear.
I'm hoping someone could help me out with a file I have at work that is taking lots of time. We get a report with multiple division's information on it, and we have to filter down for each division on each tab (removing the other divisions), and then email out the files to that respective division.
I've been digging around on the board, and I know it's possible to do what I'm trying to do via VBA, I'm just a little lost at where to begin.
Overview:
There are three data tabs in the file, Summary1, Proposal1, and Mgmt1. I get all of the data in these files and have to filter down to each division and delete the other division's information. Once I do that, I have to refresh a pivot table, save the file, and then email the file as an attachment to a pre-defined group.
Detail:
The file starts with a "Summary1" tab, and the division starts in cell A4, going down. There is a second tab in the workbook with the same division in cell D4, going down (sheet name is "Proposal1"). On the Proposal1 tab, there are a series of companies listed in column E. In order to filter the last tab, "Mgmt1", you need to lookup the remaining values in column E in the Proposal tab after you've filtered, and delete the "N/A's" (companies to lookup are in column A for the "Mgmt1" tab). The data is arranged on the Mgmt tab so you could do a vlookup in column I and then delete it after the lookup and filter has occurred.
Lastly, there is pivot table on the tab "Pivot", the Pivot table name is just "Pivottable1". This needs to be refreshed after the filters have occurred.
Once the data has been filtered, the file needs to be saved to a location, we'll use "C:/Sample/" for this example, with the division's name on it. The file then needs to be attached to an email and sent to the division (really just need to use the display versus send functionality so I can review the files before sending).
On the spreadsheet, I've created a tab named "Email_Details", and put the division names in column A, the "To" recipients in column B, the subject in column C, and the body in column D. If a division occurs in the Summary1, Proposal1 or Mgmt1 tabs that isn't on the email tab, I'd like an error message to pop up telling me to insert the data before resuming. If one of the divisions isn't on the first three tabs, but is in my email tab, that can be ignored. I'd also need to hide the "Email_Details" tab, or better yet delete it.
I'd like the code to loop through each division, filter down the results (and delete the others), save the file and create the email.
Thanks so much in advance and sorry for the long post! I'm happy to share a file if easier to look at the information that way or if something isn't clear.
