Separate large data set onto multiple tabs

[crimsonexcel]

Hi,

I have a large data set (150,000 rows). Each row has 16 columns. I need to separate this data set onto separate tabs based on the text description in column E.

For example there may be 2,000 rows with a description like "Swedish actions in the second world war" I need for excel to recognize the country in the description "Swedish" and pull all rows like this to a new tab in the document called "Swedish". I actually already have the tabs created (about 50 countries) but realize it may be easier to start from scratch.

To further the example, the next 8,000 rows might have titles like "British icons in the 18th century" or "British revolutions and their outcomes". These 50,000 rows may have different descriptions but they are all common in that they start with "British" Again, would need to pull this data to a new tab called "British".

It looks like the first word of every description line is in fact the country if that is at all helpful.

To slightly complicate things, I have around 60 excel files that are all 500,000 rows and need to be sorted this way. Ideally I could drop a master data list (i.e. 1 file) into the first tab of a master document with all of these tabs and have it sort into each tab. I would then repeat this with the next file so that in the end, I have one master file that has everything sorted into tabs (with data collected from all 60 excel files)

Obviously realize I will probably need to do some manual labor to make this happen, just trying to cut my work time down from weeks to days or hours.

Any help would be much appreciated. I have not posted before, so please let me know if you need further information.

Thanks!

 

[crimsonexcel]

Now it is giving me:

Run-time error '9'
Subscript out of range

and is highlighting this line in yellow:

wsMaster.Rows(1).Copy Destination:=Sheets(CountryNames(i) & "(1)").Range("A1")

 

[MrKowz]

Change that line to:

wsMaster.Rows(1).Copy Destination:=Sheets(CountryNames(i) & " (1)").Range("A1")

 

[crimsonexcel]

Again getting run-time error '9'
subscript out of range

but this time for line:

Sheets.Add After:=Sheets(CountryNames(x - 1) & " (" & overflowcount - 1 & ")")

 

[MrKowz]

What is the value of overflowcount when that errors?

 

[crimsonexcel]

MrKowz

Apologies for the delay in response, I had to step into a meeting at 1PM.

Is there a place I should look for the value of the overflow? It doesn't say anything about an overflow value in the error message text, just the run-time error '9' and then highlights the line I posted in my last post in yellow.

Is there somewhere I should love for this value?

 

[MrKowz]

When it errors, mouse over overflowcount, it will show you the value.

 

[crimsonexcel]

So somehow it started working, but in the status bar you created in the bottom left it is telling me, "Currently moving row 17 of 17 in budget.xlsx" (which is another excel file on my desktop)

It did create all of the tabs and named them correctly but it seems to be trying to move rows in completely different files.

 

[MrKowz]

You need to put the file (and all the files you want to consolidate) into a folder where they are the only files. This macro goes through and opens every .xls file in the same directory (otherwise, how does it know what files to open)

 

[crimsonexcel]

oh...sorry, didn't realize you built in the functionality to consolidate as well, thought it was just the deleting feature.

Just ran a test with four files in one folder and it gave me the run-time error I mentioned earlier.

When I debug and mouse over "overflowcount" in the source code it give me "overflowcount = 1". The progress bar you created says "currently moving row 8740 of 446,232 in file 10.xlsx" (one of the four files)

 

[MrKowz]

Hmm... go look at row 8740, what country does it show? It sounds like it is finding a country that was not previously on our list (or perhaps it is not spelled the same as one on the list).

CountryNames = Array("Argentine", "Australian", "Austrian", "Belgian", "Brazilian", "Bulgarian", _
                     "Croatian", "Cypriot", "Czech", "Danish", "Dutch", "English", "Finnish", _
                     "French", "German", "Greek", "Hungarian", "Irish", "Italian", "Japanese", _
                     "Mexican", "Northern Ireland", "Norwegian", "Polish", "Portuguese", "Romanian", _
                     "Russian", "Scottish", "Slovakian", "Slovenian", "Spanish", "Swedish", "Swiss", _
                     "Turkish", "Ukranian", "UnitedStates", "Welsh")