Create Multiple Files From Columns

[tjs03e]

Hello,

I have a workbook with client services. In column A I have a list of all possible services (59 rows). I have columns B through JJ with client name in Row 1 and below I have the data corresponding to the services they received according to the services listed in column A.

I need to create a separate workbook for each client. In each new workbook I need the services listed in column A and in column B I need the client name in row 1 and the appropriate data listed under that client in the master workbook.

I basically need the code from this thread:

http://www.excelforum.com/excel-prog...from-rows.html

only transposed so that it creates multiple files from columns rather than from rows.

Each new file should use the client's name from column B for it's name.

Thanks for any help.

[arlu1201]

Can you please attach the workbook? I can help you out with the code.

[tjs03e]

Here it is attached. Thanks

[arlu1201]

I had a look at the workbook. It has like 268 columns while the rows are just 56. Wouldn't be more practical to have it transposed at the first instance itself when you are populating the data? This way, if you have more clients to add, you can always do it.

This way, you will be able to use the code that you found in the thread that you provided above.

Let me know your thoughts.

[tjs03e]

Thats how I had the data originally, but the purpose of this data is to create a file for each client in which the first column is the services we provide and then each column to the right will be updated with monthly numbers. Last month I did use the code from that linked thread and then created a script to transpose each file, but I had to run that script manually for each file. I was hoping to cut out that transpose step this month.

Because of the end product I need to create an individual file for each client each month and then combine those files.

Unless there's an easier way in which I can update each client workbook with monthly data? But adding new clients would pose a problem because I wouldn't have a file for that client from previous months.

What do you think?

[arlu1201]

How about this? I can build a logic so that we can have separate files for each column and in each new file, populate the data for each month in a different column. This will save you the trouble of combining the files as you said below:

Because of the end product I need to create an individual file for each client each month and then combine those files.

[tjs03e]

That would be great! Thanks so much.

[arlu1201]

I have a question. I had suggested that i will build a code in which the monthly data will be inputted month wise per client in their own workbook. How will i know for which month the file belongs to? Is there some identifier? OR should i just ask you to open the file for the month in question via code?

[arlu1201]

I have created this code for you to copy the data into multiple files. It works fine, however you need to ensure these things:

1. I have renamed the file as Client_Data. You can change this in the first line of your code.
2. I have used a directory called D:\Data\ to save the output files. You can change it as per your system.

This code copies the respective client data into their respective workbooks. If you need to enhance this further (to include monthly data), we can discuss it further.

Please Login or Register  to view this content.

[tjs03e]

Hey, thanks for getting this back to me

I'm getting a Run-time error '9' error on the following line:

file_name = Workbooks(fname).Worksheets("Sheet1").Cells(1, i).Value

[arlu1201]

Have you named the file containing the macro as "Client_Data.xlsm"? Or if you are using any other name, you need to put it in the first line of code.

Also, Sheet1 should be Sheet1. Do not change it. If at all, you need to do so, change it in that line.

[tjs03e]

I changed the first line of code to match the name of the working file. The sheet is Sheet1.

The error I'm getting says subscript out of range

[tjs03e]

I cleared up that error, now I'm getting subscript out of range error on the following line:

With Workbooks(file_name & ".xls")

[tjs03e]

I worked around a couple of problems and got it to work beautifully...thanks so much!