VBA- copy data from range for specific criteria and paste into new workbook.

[Todd_H]

Hello all,

I am wondering if someone could assist on proving a VBA code that would assist.
I have a large report with numerous employees on it. I need each employee to have their own workbook. In column A is their employee id. There is multiple rows with the same employee id on it and i want to copy the range, columns A:F for the same employee id and paste into a new workbook, the number of rows varies for each employee but columns A:F reamin the same. Each time there is a change in employee id on the report I want to copy the range for that employee again into a new workbook until at the end of column A.
Is this possible? Or am I too ambitious, if possible, would someone be able to provide the vba code that would make it work.
Thanks in advance

 

[My Aswer Is This]

My personal opinion if you want it is:
Having a separate workbook for each employee may make things difficult for you.
If you employer, ask a question about 5 different employees you will have to open 5 different workbooks to find the information.
I would think of another way to do things.

Why not a separate worksheet for each employee? All in the same Workbook.

 

[Todd_H]

Further to this, if possible, if the workbook after data copied to it can be saved as, with the name as either the employee idnor employee name, the employee name is in column b

 

[Todd_H]

My personal opinion if you want it is:
Having a separate workbook for each employee may make things difficult for you.
If you employer, ask a question about 5 different employees you will have to open 5 different workbooks to find the information.
I would think of another way to do things.

Why not a separate worksheet for each employee? All in the same Workbook.

Thank you for the reply, i did originally think of sheets instead of workbooks however this report is the beginning of a much larger project. The current report has 500,000 rows of data with 3,100 employees on it, might get messy and too large with 3100 sheets . If each workbook name was named as the employees name then if needed locating a employee would be easy to find by looking in the folder and searching alphabetically.

 

[My Aswer Is This]

I did not understand this:
You said:
"Each time there is a change in employee id on the report I want to copy the range for that employee again into a new workbook until at the end of column A."
Sounds like you would then have more then one workbook for the same employee.
And what does change mean?

More then likely someone else here on the forum will have another ideal but for now
To write a script we would need to know the exact Path where you want all these Folders saved to.

And I would assume if employee id in column A was 123456
You would want the Workbook name to be "123456"
Is that correct?

And you said:
"with 3,100 employees"

To have a script create 3,100 workbooks and save them some where would be a large task:
I'm sure someone else would need to help you.
I will keep watching and see what I can learn
And does it have to be only columns A to F or can we not copy the entire row.

 

[Todd_H]

So sorry that I haven't explained properly. I think I am being too ambitious. The rows can be copied instead of Range A:F.
To explain further, the report I have consists of 3100 employees, each having varying amount of rows, column A is their employee id, which consists of numbers eg. 123456 in column B is their name, John Smith, in the report for example the first 50 rows on the report could be for the one person so I would want those 50 rows to be copied to its own workbook and saved as, employee id or employee name, in location for example C:/Desktop/Project. Then what I mean by change is, when the employee id changes from that person to the next and that person has for example 100 lines, I want those 100 lines to be copied to its own workbook and saved as again as that employee id or employee name.

 

[My Aswer Is This]

Yes I think I understand correct me if I wrong

In column A of sheet named "Master" you have employee id's
Like 123456
Like 245678
So you want a script to look down Column A and find all the id's 123456 and created a New workbook named "123456"
And copy all those rows with "123456" to Workbook "123456" sheet(1)

And on and on:
But since you said you have 3100 different employees we would neeed to create 3100 workbooks. Correct:
You earlier said:
"The current report has 500,000 rows of data with 3,100 employees on it,"

 

[Todd_H]

Yes I think I understand correct me if I wrong

In column A of sheet named "Master" you have employee id's
Like 123456
Like 245678
So you want a script to look down Column A and find all the id's 123456 and created a New workbook named "123456"
And copy all those rows with "123456" to Workbook "123456" sheet(1)

And on and on:
But since you said you have 3100 different employees we would neeed to create 3100 workbooks. Correct:
You earlier said:
"The current report has 500,000 rows of data with 3,100 employees on it,"

That is correct, i have attached a snip of what the report looks like, i have changed names and employee IDs for privacy reasons.
To make it easier, i could add the employee numbers (all 3100) to a seperate sheet so the code can work through the list and create the workbooks. I beleive it would be easier to have the workbook named as the employee name so i could have the name next to each employee number in the sheet with the list.

 

[My Aswer Is This]

Glad to hear your explanation. Now that you want to use Employee Name you may want to explain what column employee name is in?
I think it may be Column B but not sure.
There are some restrictions on workbook names I'm but not sure.
If employee name is show as "John H Doe" I'm not sure if you can name a workbook that way
As I said before I'm not sure I'm the one here who can help you so I hope someone else here will provide a answer. Creating nearly 3100 new workbooks and dealing with nearly 500 thousand rows of Data is way to difficult for me. But hopefully someone else may be able to help.
And you mentioned:
"however this report is the beginning of a much larger project. The current report has 500,000 rows of data with 3,100 employees"

If you think the number of rows and employees will get much larger I think Excel only has 1.5 million rows per sheet.