Proceed Data and save in different files

**norriscmk** · 06-13-2007, 09:08 AM

I've been so frustrated at work working with some many worksheets!! All of my colleagues and I really need experts to help.

Below is the worksheet similar to what we used to proceed

Company Name | Model # | Comments |
ABC | 1234 | Good |
DFG | 3245 | Bad |
IUR | 8950 | Moderate |
ABC | 2435 | Bad |
IUR | 2432 | Moderate |
.
.
.
(could be >100 entries under 20 companies)

We need to group the above data by company, copy it into a new file, and send it to respective company...

Any macro can serve this purpose? Save .xls to a location named by company name will be perfect as we'll send the files by mail merge to email with attachment.

In addition, the headers need to be included in the new files too...

It could be done in different approach/logic as long as I can get the things done...

MILLIONS THANKS TO YOU!

**boylejob** · 06-13-2007, 03:50 PM

norriscmk,

Could you post your workbook or at least a sample of your workbook so I get a better idea of what you are wanting to do.

I'm assuming that you want to take this

ABC | 1234 | Good |
DFG | 3245 | Bad |
IUR | 8950 | Moderate |
ABC | 2435 | Bad |
IUR | 2432 | Moderate |

turn it into this

ABC | 1234 | Good |
ABC | 2435 | Bad |
DFG | 3245 | Bad |
IUR | 8950 | Moderate |
IUR | 2432 | Moderate |

and then create a sheet for company ABC, company DFG, and company IUR with each sheet having a common header accross the top.

Are you wanting to create a separate worksheet for each company or are you wanting to create a separate workbook for each company.

I might be able to help you on this project once I see exactly what you are wanting to do.

**norriscmk** · 06-13-2007, 09:11 PM

Hi Jeff,

You are correct. I need to have data in seperate "files" not just sheets. Thanks for your help.

Norris

**boylejob** · 06-13-2007, 09:36 PM

norriscmk,

Did you solve your problem? I was willing to help, but needed to make sure I was clear on what you were wanting done and needed a little more information.

If your problem has been solved, great, but if it hasn't, please post your workbook or at least a sample and I will gladly try to help you.

**norriscmk** · 06-13-2007, 11:11 PM

Hi Jeff,

I definitely need your help on this.

I don't have my work sheets with me right now as i'm away from office but the data structure is the same as what you said in the previous message. "Company name" "Model #" ....

I need to sort out by "company name". All rows with this company name will be copied and pasted to a new workbook with table headers and save as the name according to the company name. The attributes in a record can be up to 40.

Thanks for your time on this.

Norris

**boylejob** · 06-13-2007, 11:17 PM

Norris,

I'll see what I can work up for you. When you get back to your office, it might be helpful if you can post a sample of your workbook. It doesn't have to be actual data, but should closely resemble what you are working with. This will allow me to test things.

**norriscmk** · 06-14-2007, 12:09 AM

Originally Posted by boylejob

Norris,

I'll see what I can work up for you. When you get back to your office, it might be helpful if you can post a sample of your workbook. It doesn't have to be actual data, but should closely resemble what you are working with. This will allow me to test things.

Thanks！Here comes the attachment

**Steel Monkey** · 06-14-2007, 02:06 AM

Hi Norris

Please see attached file and let me know if this is what you wanted

Please ensure that there is a folder called Company_Data in the directory where you save this spreadsheet(it is very rough at the moment and i dont have time to pretty it up at the moment)

Have a fiddle around with this and let me know how it goes!

**norriscmk** · 06-14-2007, 08:03 AM

Originally Posted by Steel Monkey

Hi Norris

Please see attached file and let me know if this is what you wanted

Please ensure that there is a folder called Company_Data in the directory where you save this spreadsheet(it is very rough at the moment and i dont have time to pretty it up at the moment)

Have a fiddle around with this and let me know how it goes!

Hi pal,

Thanks for your help. The result is what I need.

Here comes another issues... what if the header consists of 2-3 rows... (See Attachment) Can we add any variables on this? Millions thanks for your help.

Cheers,
Norris

**boylejob** · 06-14-2007, 08:17 AM

Norris,

Do you mean that sometimes the Header might be 1 row and other times it might be two or three?

My thought was that you wanted something that would automatically sort the records and then create all the new workbooks with the click of a button. Is that was your were wanting? That is what I was working on for you.

**norriscmk** · 06-14-2007, 08:59 AM

Originally Posted by boylejob

Norris,

Do you mean that sometimes the Header might be 1 row and other times it might be two or three?

My thought was that you wanted something that would automatically sort the records and then create all the new workbooks with the click of a button. Is that was your were wanting? That is what I was working on for you.

Hi Jeff,

Thanks for your reply. To certain extent, you are correct. It's what I need. But can I say that the headers won't more that 3 rows. Can we have the header set as 3 rows, all ranges starts from the 4th rows? For table with one row in the header, I can add 2 rows on the top of it to fit the marco to copy and paste the header to a new workbook. Thanks, Norris

**boylejob** · 06-14-2007, 09:11 AM

Norris,

I believe the header thing can be worked out fairly easily. I will always start looking for data on row 4. I will also check for data in the first cell of each of the first three rows and this will allow for the proper header to come over.

Last question ... maybe! Will your number of columns always be the same. The sample data had 11 columns. Is that going to be constant at this point.

Hopefully, I will have something for you before the end of the day. I am not sure where you are located but I am in the USA and it is currently about 9:11 am on Thursday

**norriscmk** · 06-14-2007, 09:19 AM

Originally Posted by boylejob

Norris,

I believe the header thing can be worked out fairly easily. I will always start looking for data on row 4. I will also check for data in the first cell of each of the first three rows and this will allow for the proper header to come over.

Last question ... maybe! Will your number of columns always be the same. The sample data had 11 columns. Is that going to be constant at this point.

Hopefully, I will have something for you before the end of the day. I am not sure where you are located but I am in the USA and it is currently about 9:11 am on Thursday

Hi Jeff,

I'm 100 miles away from you in Hongkong; it's 9:xxpm on Thursday. Hopefully I can get your file tmr morning in the office.

The # of column will always change.

Really thank you for your time.

Norris

**boylejob** · 06-14-2007, 07:45 PM

Norris,

Here is the program using the test file that you sent. There are few things to keep in mind as your run this program.

1. It is currently storing thing in the directory C:\Temp, but you can easily change that in the code.

2. When you plug this code into the actually file your are going to need to change this line of code to match your file.

Please Login or Register  to view this content.

3. The program is always looking for data in your main file to begin on row 4.

4. Program is looking at the first 3 rows as being the header, but if only 1 or 2 of those lines are used it will adjust the header on the new workbooks.

I hope this is what you are wanting or will at least get you moving in the right direction. I don't always code in the most efficient or fancy manner, I try but it doesn't always workout that way. The bottom line is the code works on the test file and will hopefully work for you.

Thanks for the little challenge!

**norriscmk** · 06-15-2007, 12:34 PM

Originally Posted by boylejob

Norris,

Here is the program using the test file that you sent. There are few things to keep in mind as your run this program.

1. It is currently storing thing in the directory C:\Temp, but you can easily change that in the code.

2. When you plug this code into the actually file your are going to need to change this line of code to match your file.

Please Login or Register  to view this content.

3. The program is always looking for data in your main file to begin on row 4.

4. Program is looking at the first 3 rows as being the header, but if only 1 or 2 of those lines are used it will adjust the header on the new workbooks.

I hope this is what you are wanting or will at least get you moving in the right direction. I don't always code in the most efficient or fancy manner, I try but it doesn't always workout that way. The bottom line is the code works on the test file and will hopefully work for you.

Thanks for the little challenge!

Hi Jeff,

The macro works perfectly well! All of my colleagues thank you for the program. You are really great! I owe you this!

Thanks,
Norris

**boylejob** · 06-15-2007, 12:41 PM

Norris,

I am so glad it worked for you and your colleagues.

It was my pleasure to be of assistance!

**norriscmk** · 06-18-2007, 08:41 AM

Hi Jeff,

Here comes the real problem.

Today, my colleagues found out that the macro works with all tables but not the one in the attached... we have problem in "sorting" the data and copying headers in this attachment.... No code was changed. Please let us know why it happened

Also, there are always error in 'creates the borders around the data

.Borders(xlInsideHorizontal).LineStyle = xlContinuous

I've resolved this problem by deleted all codes under 'Creates the borders around the data.

Norris

**boylejob** · 06-18-2007, 02:45 PM

Norris,

I have made some modification to the program which will hopefully solve all the problems you have listed.

I believe the code was unable to handle things if the last company only had one line of data. I have modified the code to tackle this problem from a different angle.

You were probably having problems with this on any company with only one line of data because you cannot have an InsideHorizontal with only one row selected. I have added some code to look for the single row condition so that should solve the problem.

Please Login or Register  to view this content.

I am not sure about the "sorting" data problem unless it was just the result of one of the other problems.

Anyway give this modified version a try and lets see if there are any other bugs to be worked out. I would suggest you go back an rerun anything you have already done because it may not be correct. I apologize for any inconvenience this might have caused you.

**norriscmk** · 06-18-2007, 07:00 PM

Originally Posted by boylejob

Norris,

I have made some modification to the program which will hopefully solve all the problems you have listed.

I believe the code was unable to handle things if the last company only had one line of data. I have modified the code to tackle this problem from a different angle.

You were probably having problems with this on any company with only one line of data because you cannot have an InsideHorizontal with only one row selected. I have added some code to look for the single row condition so that should solve the problem.

Please Login or Register  to view this content.

I am not sure about the "sorting" data problem unless it was just the result of one of the other problems.

Anyway give this modified version a try and lets see if there are any other bugs to be worked out. I would suggest you go back an rerun anything you have already done because it may not be correct. I apologize for any inconvenience this might have caused you.

Hi Jeff. You don't need to apologize for anything. This project, I tell you, will be helping more than 100 people around me, we are all relying on you. We'll continue the testing and feedback to you. Thanks a lot for your great help.

Norris