Need Macro Help with separating employee data by manager

[mscirill]

I have an excel file that includes a list of employees. Each employee is assigned to a manager.
I need help creating a macro that will separate the employee list into individual worksheets by manager.

The excel headers are:
Region, District, Store, Associate Title, EmpID#, Current Hourly Rate, Review Rating, Increase Guideline, Percent Increase, Increase Amount, New Hourly Rate, Comments, Position Date, Hire Date, Manager

I have approximately 300 managers with 23,000 employees. How can a create a macro to separate each manager into its individual worksheet?

[BigBas]

I assume the file posted is the "output" file, with one file corresponding to each manager. Is that correct? If so, can you give some details on the "input" file, which I assume is some sort of a data dump with all of the employees current details? For example, does data start in row 1? Are there headers? If you want to post a small sample of the actual file (use fake names), that would also help.

Finally, do individual sheets for managers already exist? I assume each manager will get a separate workbook, and not a separate tab. Is this correct? If so, do you need the input file to automatically create a new workbook per manager?

Sorry for all of the questions, but with such a large file, the last thing we'd want to do is mess up and start from scratch

[mscirill]

Yes, this is the output file, but its also the input file as well. This file will include all the employees and managers in the same file, the last column will have the manager's name. The data doesn't start on row 1, it starts on row 17, due to other headings at the top.

Each manager will get individual worksheets not separate tabs. I do not have worksheets already created for them. I need the new input file to appear like the output file but with on the assigned manager.

Any help will be greatly appreciated.

[BigBas]

Try using the following code. A couple of notes: Column H must be unhidden. In Excel, trying to copy a merged cell when one of the columns hidden will throw an error. The code will throw an error if you dont unhide it.

Also, please keep in mind 300 worksheets (1 per mgr) within a workbook is HUGE. The code may take a long time and may even crash even you don't have a powerful computer. Give it a test with a smaller sample set first to see if it does what you want, then try it on the larger data set.

Let me know of any issues

Please Login or Register  to view this content.

[mscirill]

I'm so clueless to Macros. When I run the macro, I receive an error stating: "Method 'Name' of object '_Worksheet' failed.

Its referencing the line: wsMgr.Name = cel

What do I do now?

[BigBas]

It looks like the script is running into an error when trying to assign a new Worksheet with the name of the manager. My best guess? The sheet name already exists. Have you already created a Worksheet for each manager name? If so, have you tackled ALL manager names, or are only some of the managers assigned?

[mscirill]

Unfortunately, no I haven't created a worksheet for any if the managers yet.

[BigBas]

Hmm, that's odd. It becomes difficult to diagnose the problem without actually seeing it.

Try this. Attached is my code within the sample workbook you provided earlier. Try running the code in this workbook and see if you still get the error.