Taking rows containing specific text and moving to specific tab.

[X82]

Our document contains around 40K entries or various geographical locations (64 different locations in total). Each of these 64 locations belong to one of 10 major areas. Currently we have a tab for each of these 10 major areas. We have to (each month) look through the first column and match up with of the 64 locations to decide which major area it falls under. We cut those rows and have to paste them into the correct tab which is named after the major area.
In my head it looks simple to automate, if cell A1 contains Cumbria, select the entire row and move it into the major area which looks after Cumbria.

Below is an example, since I am at work and unable to upload any examples or post any screenshots.

So, 10 tabs names Jan to Dec if any cell in the first column contains Alpha or Beta, move the entire row into tab Jan.
If any cell in the first column contains Charlie or Delta, move the entire row into the Feb tab.

Repeat for the entire list.

There may be some issues as some entries are all uppercase or lower, so I need to capture both. There may also be a need for wildcards. I.E if it contains County BLANK or North BLANK, like North Wales or County Cork.

Can anyone help me with this mission? We currently have to do this manually and the staff are ready to strike!


Edit: I meant to say that if the 10 tabs can be auto generated that would be fab. The end game is a one button solution to do this boring task.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Note requires an index sheet to be created - see sample attached.

[amartinez988]

Hey There X82,

I might have a possible solution for what I understood was your problem.

First:

Create a sheet that will have all you countries in column A and the Areas for each country in column B. Something like this:
(This is my Sheet2, only for the reference table)

Sheet 2.PNG

Of course create all the Sheets for those area with same name that we have in column B.

Once you have everything set up use the code:

Please Login or Register  to view this content.

Please let me know

[X82]

Hey There X82,

Please let me know

That looks great!
One thing, some of the countries in the example have multiple areas for each. How can I input that into things?

[amartinez988]

OKKK... you almost got me lol
Ok add as many Areas as you want next to column B and used this code:

Please Login or Register  to view this content.

NOTE: You might need to clean it out a little bit to make work for you but at least is reference

[X82]

Great! I will try this at work tomorrow and report back.
One other thing, which is a bonus.
In an ideal world, I want the reference file to be a total seperate spreadsheet on our network drive. So the script will find the references inside that file. That way someone can open up the data sheet with thousands of records, hit the macro button and it will work (although the next step would be the auto creation of new tabs for each "country". But that is end-game material. I appreciate this already.

[amartinez988]

Yes X82. you can do that! However I will recommend you to have the reference in the workbook with the Sheet VeryHidden. This will not allow people to unhide the sheet unless they go trough the IDE.

Please don't forget to mark this post as SOLVED when all your doubts are finished and add reputation to any member that offered you help

[X82]

Hi there. I am running into issues, which are 100% my own lack of knowledge.
My current spreadsheet is laid out like:
My Locations tab which has column A of countries and B through to AL of areas.
My 2nd tab called Data contains everything I need to scan and move. The relevant details are in the first column and go all the way down until something like A36883
My final tab I made is called Output which I initially wanted the results to move to since I have not yet created the tabs for each country.

As soon as I run the code it highlights the line:

Please Login or Register  to view this content.

So, my locations are on Sheet2 (according to Visual basic)
My data is on sheet3 and output Sheet4
Sheet1 is a hidden sheet for my reference.

Any ideas? I feel I am being dumb here.

[amartinez988]

Ok X82,

Lets fix it little by little It should give you different problems until you make it fit for your workbook.

First:

Name your Data sheet (tab), "Data". Replace code:

Please Login or Register  to view this content.

Next, fix this code replacing Sheet2 with the name that you gave to the sheet where all your Countries and Areas are located. Also re-dimension the range of the table from Z10 to AL10. (That number 10 should be 64 since that's the amount of countries you have. If you have more than 64 rows adjust this value)

Please Login or Register  to view this content.

also here and anywere else where you have these values:

Please Login or Register  to view this content.

Lets start here. You might need to fix more stuff but since I can't see the workbook it has to be this way

Don't forget to create the sheets with the name of the areas or it will cause errors.

Last mention: We don't care how many rows we have in the sheet Data since it is getting the last row in column A automatically. Be aware that the Code deletes this rows once it places them in their respective Area sheet.

Advise

[X82]

Many thanks.
I tried all corrections and the script errors in the exact same place.

Subscript out of range:

Please Login or Register  to view this content.

[amartinez988]

OK, I'm almost positive that the error is a product of not having the Sheet Data in the workbook that you are running the code.

My next question would be: Is the code is inserted in a Module inside the workbook that has the Sheet Data?

This error only occurs when the Sheet is not in the workbook that you are referencing.

[X82]

Ah yes, that solved the first part indeed.
However it now errors at this point:

Unable to get the match property of the worksheetfunction class

Please Login or Register  to view this content.

[amartinez988]

Ok Here

most likely the problem is that you dont have the Country in sheet get from Sheet Data (column A) in the Sheet Locations (Column A).

If you are Getting the Error in the first loop, probably you have a value after the last Country in sheet Data that is trying to find in sheet Locations. Remember that in column A (Sheet Data) the loop is getting all values as countries and matching them in column A of sheet locations.

This is the first check we need to check.

[X82]

I need all the entries in the first column in Data to each go into the corresponding tabs as outlined in Locations tab.

This is how it would start and this is how I ideally would like it to look (I've only done a few tabs because it's tedious, but hopefully you get the idea).

[amartinez988]

I fixed the Code for you and also the Locations Tab.

Let me know

[X82]

I'm hoping for some help here.
The code which I am now using works, but to a point.

Here is the current code:

Please Login or Register  to view this content.

When I run the code it does indeed copy all the entries to the correct tab, but each rows details have been shifted.

for example, this is how the original layout is:

Column A Column B Column C
Alpha Alpha Alpha
Bravo Bravo Bravo
Charlie Charlie Charlie

This is how the it ends up once I run it:

Column A Column B Column C
Alpha Bravo Charlie
Bravo Charlie Bravo
Charlie Alpha Charlie

Suffice it to say, the end result is just broken. I found this out the hard way after I ran the code on a huge sheet and sent it to the boss.

If anyone needs further clarification let me know, I really would love this to be fixed.