Separate Huge Worksheet into Multiple tabs

[GingerLeake]

Every week I get a huge (40,000+ row) Excel 2010 worksheet that I manually separate into different tabs for management.

There are multiple fields we use for the tabs, starting with column K, called 'Dept'. In that column, I pull according to 'CDD Services', 'KYC Approval', or 'error'. Each of those three have their own tab, named 'CDD Services, 'KYC...', etc. Additonally, I sort by 'Task Owner Manager', column R, name the tab after the names found, ie 'Joe Blow', 'John Doe', etc. Each row of data has 30+ fields but I only take about 10 of them into the new tabs. (I hope this makes sense!)

This takes time and I know someone out there can help me with some VBA that will make this a snap....Right? I've been playing around with some but haven't quite gotten it down yet and I could really use some help.

[arlu1201]

This can be done. Do you have a sample file showing how the data is and how you want the output to be?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[GingerLeake]

This is a very small version of my workbook but I hope it's enough information to show you what I need. Thank you so much for wanting to help!

[arlu1201]

Do you want the dept segregation and employee segregation to happen in the same file or do you want 2 workbooks 1 for each type of segregation?

Also, which are the columns that you want excluded?

[GingerLeake]

No, just one workbook. The users can subtract what they want from there.

All columns are on Sheet1 and 'Sorted by Dept' and what I need is on 'Koeppel,Kim M' tab. Let me try to break it up like this:
Column A, no.
Col B, yes.
C, D, E, F, G, H, I, J, K, L, M, N, yes.
O, no.
Q, R, yes,
S, no.
T, yes.
U, no.
V, yes.
W, X, no.
Y, Z, AA, yes.
AB, no.
AC, AD, yes.
End of data.

[arlu1201]

So the limited columns is only for the individual employee sheets right? The dept sheets should have all the columns?

[GingerLeake]

Whoops! No, glad you asked! The dept tabs also should have the shortened version, I just didn't cut the unnecessary fields out of the copy you received.

[arlu1201]

Just one last question - your sample file shows the output as 2 tabs, but actually you want individual sheets per person + per dept right?

[GingerLeake]

Yes, there are three possibles for dept tabs and then a dozen or more possibles for the name tabs. If that is a problem, we can shorten the names tab and use the manager names instead, in which case there would be 8 or less. That column would be R on Sheet1, called 'Task Owner MGR Name'

[arlu1201]

No, it wont matter from the code perspective at all. It may only increase your file size.

So you only want the split wise sheets for the dept and the employees? Not the ones you showed in the sample file right?

[GingerLeake]

Well, the file I sent you had three tabs, the first one being the raw data as I get it (Sheet1). Then the second tab was 'Dept' which is what I pull out before I start separating it, and that included all three of depts I pull--'CDD Services', 'KYC Approval', and 'error'. Each of those three have their own tab, named 'CDD Services, 'KYC Approval' and 'error'.

Additonally, I sort by 'Task Owner Manager', column R, name the tab after the names found, ie 'Joe Blow', 'John Doe', etc. On the file sent, I included one employee, the one named Kim to show you how the names were listed. It would be great to have each of the managers have their own tab but if we can just show them 'dept' that would be alright.

[arlu1201]

We can surely have the split however you want it.

So if you want the first set of tabs to give you a dept split and then the next set of tabs to give you the individual split, that can be done easily. Any other splits you would need or any other summaries?

[GingerLeake]

That would be all.

[arlu1201]

Try this code - the only problem being there are blank cells in column R, how do you want to handle this? The macro will give you an error for the blank cell.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[GingerLeake]

I'm getting a type mismatch error and this line is highlighted when I click Debug.
If Not Evaluate("ISREF(' " & sname & " ' !A1)") Then

And the very first tab opens up, butit's 'Front Office' and it is not one I need. For Dept, I just need the three listed above (CDD Services, KYC Approval, and error.) All others do not pertain to our area.

And when it comes to blanks, if a case is listed on the CDD Services tab but it doesn't have the "Task Owner" or "Task Owner Manager" fields filled out, it just means that it is unassigned and can stay on the CDD Services tab. Or, if it has to be sorted in order for the macro to run, it could go onto another tab called "Unassigned."

However you have to do it is fine with me. And thank you so much for your help! I (and my managers) really appreciate it!

[arlu1201]

Ok, so its only those 3 depts that need to be segregated? I thought its all the depts listed. I will add this to the code.

I will move the blank cells to the unassigned tab.

[arlu1201]

Try this updated code

Please Login or Register  to view this content.

[VKS]

Hello GingerLeake, Did u consider a Pivot Table for making the desired report(s)...
Best Regards/VKS

[GingerLeake]

That was wonky. It created the CDD tab, then did David S, Joseph C, Unassigned, other manager names but skipped KYC completely and went to the Error one instead. Also the data looks okay on the CDD tab but starting at the first manager name, some of the data is misaligned.

I am just not good enough at this though I have been trying all morning but could you abbreviate this code and just make it separate into 4 tabs, still by department, column K on Sheet1: CDD Services, error, KYC Approval, 'Remaining'. And just columns B, D, E, F, G, H, J, K, L, Q, R, S, U, V, X, Y, and AC. That's shortened a bit too, so maybe it will be less likely to become misaligned.

[GingerLeake]

No, I've never worked with PivotTables but did have a little experience with VBA which is why I wanted to go that route.

[VKS]

Since you hvnt worked with Pivot Table i am attaching a pivot table for your ref. If you can show me how do you want your desired output sheet to look like then may be i can try and slice and dice bit more. Please note pivot table also lets you take page fields in to individual pages.
Best Regards/VKS

[GingerLeake]

Thanks, VKS. That looks nice. I guess that means I wouldn't have to put in the formulas (CountIf, CountIfs, etc) to find out what was under which area or person?

But I need it to look like the Sheet1 layout with all of the data there so the analysts can work the cases. If you followed the thread, there ended up being over 20 fields that I asked Arlu to configure for me and it feels like we're almost there so I'd rather use Arlu's VBA for this part. The next time I need the totals of something though, I would definitely look into pivot tables.

[arlu1201]

That was wonky. It created the CDD tab, then did David S, Joseph C, Unassigned, other manager names but skipped KYC completely and went to the Error one instead. Also the data looks okay on the CDD tab but starting at the first manager name, some of the data is misaligned.

I designed the code in such a way that i wont have to review the row a 2nd time. So i go thru each row and if it belongs to the 3 depts that you have chosen, it copies the row to the respective dept tab. Then it checks column R and based on the name of the person, it copies the row of data to that person's tab. If either of the tabs are not there, it will create them. Thats why you will find the CDD tab, then some named tabs, then another dept tab, etc. I can have those moved after the macro is run so they are all one after the other.

Or we can change the macro to ask it to first create the dept tabs and then move to the individual tabs.

[GingerLeake]

Ah, that explains it. So each case will either be on a dept tab or a personal tab but not on both? Whatever else happens, I must have the data on the dept tab for the individual managers so I did expect to see the rows of data on two different tabs (worksheets).

I hope it won't be too difficult to just change the macro to put the rows on the dept tabs? And instead of doing a separate tab for each person, if I just said I wanted tabs for the last names of Burtch, Caruso, Stachura, Koeppel, Jackson would that be too much trouble?

[arlu1201]

If a row has an entry in column K and it is one of the depts you want, it will show up in that sheet. Then it checks column R and if there is a value in it, it populates it in the sheet bearing that name. Otherwise, it goes to unassigned. So each row does get populated twice. After you run the macro, are you not seeing each row twice?

if I just said I wanted tabs for the last names of Burtch, Caruso, Stachura, Koeppel, Jackson would that be too much trouble?

You do not want all the managers to have their tabs but only the above ones?

[VKS]

Thanks, VKS. That looks nice. I guess that means I wouldn't have to put in the formulas (CountIf, CountIfs, etc) to find out what was under which area or person?

But I need it to look like the Sheet1 layout with all of the data there so the analysts can work the cases. If you followed the thread, there ended up being over 20 fields that I asked Arlu to configure for me and it feels like we're almost there so I'd rather use Arlu's VBA for this part. The next time I need the totals of something though, I would definitely look into pivot tables.

Glad u liked it..... Try Pivot Tables next time and who knows you also get addicted to them.....

;-)

[GingerLeake]

No, Arlu, the KYC tab for instance was never created and neither was the one for Carr, B.... If it would be simpler to just take what I really need to show and not everything, that would be great. (After seeing 26 worksheets, I realized maybe I'm being a little too greedy!)

Then on the Unassigned tab, at column N (which is O in Sheet1) the data is drawing from one column to the right so the 'Submitted by" data is showing up under "SCC". Everything I see after that is also shifted over a column.

[arlu1201]

So at present you only want 3 sheets for the 3 depts?

[GingerLeake]

If three will get it done faster, that would be fine. Once it's broken down, I can use ifs and countifs to move it to other worksheets if necessary.

[arlu1201]

I can check each code line again to ascertain where the issues lies.

When it comes to macros, anything and everything is possible. So you just need to mention what you want.

If you want, i can provide you 2 distinct macros - 1 to give you dept wise split, the other to give you individual wise split.

[GingerLeake]

Oh, yes, please! Then I could use it however I want.