Is this possible? Create new workbook from multiple sheets based on variable range?

[Ashaffer2]

I have a report that should be e-mailed to list of about 80 people each month. The master data file will be parsed into about 125 tabs based on a unique dealer code. Each person on my distribution gets a different combination of these individual dealers and I want each person to receive 1 combined report, not multiple. Also, no 2 recipients have the same combination of information to receive.

Here is my thought process - I would have a "Reference" tab with all the names of the recipients across row 1, starting in column B. Row 2 (starting in B) would contain the e-mail address of the person. Rows 3 through ? (depends and also starting in B) would contain all the dealer codes that person should receive. The amount of both rows and columns could vary, but would always start in the same place. Like this:
Excel Setup.jpg

I already have a macro to parse the data into a separate tab based on a column in the master "data" file. From here, I think I need to "build" the custom report per person in row 1 with all the dealers listed under their name in rows 3-? (variable). So essentially, I would want to build up a report all on 1 tab by combining the tabs with the listed dealer numbers (from the references), then save it as it's own temp workbook named after the recipient. Does that make sense? The last step would be to e-mail each recipient via Outlook their respective workbook to their associated e-mail address in row 2 under their name.

Whew! That makes my head hurt! I'm a VBA novice and have pieces of this. The big thing I am missing is the macro to build up and save the report. I know how to e-mail files, but it would be nice if this part was also built in. Can anyone help me with this? Or do you think there is a better way? Any help would be GREATLY appreciated!!

[Jamie.Reis]

This is incomplete and doesn't contain the mailer example, but should give you an idea of how it can be done:

Please Login or Register  to view this content.

[Ashaffer2]

Thanks for taking a look! I've tailored some of the code, but as it begins the first sheet it gives an error at the part in red below. The watch window has a value of "Nothing" for the ws. Suggestions??

This is incomplete and doesn't contain the mailer example, but should give you an idea of how it can be done:

Please Login or Register  to view this content.

[apo]

Hi..

If you can attach Workbooks showing what you're talking about.. that will make it much easier to follow and negate the need to recreate your environment.

Attaching images in an Excel forum to show your issue is undesirable.

Also.. try to avoid quoting whole posts.. it will make your thread messy and cluttered.. which will ultimately deter people from reading on..

[Ashaffer2]

Sorry about that!

Here is a sample with just a handful of tabs to work with.

Test merge sheets.xlsm

[Ashaffer2]

Been tied up and haven't gotten back to this. Any suggestions on why this is failing? See previous post for sample workbook.

Thanks for any input!