This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have 10,000 rows of data that need organized into 5 separate Excel sheets: USA, Canada, Europe, Oceania, and Asia. One of the columns of the raw data is the Country (in ISO alpha-2 format).
So, if the Country in a particular row is TW or TH, then that entire row of data would be moved to the Asia tab.
I'd like the subroutine to reference a Master Table in the backend that lists all countries and which of the 5 sheets that Country would be moved to (for example: TW = Asia, TH = Asia, NZ = Oceania, etc.).
Finally, each of the 5 new Excel sheets would need to have the original topmost Row from the original raw data (the header information).
Thank you so much!
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Here is a great way to move data into multiple sheets by the Country column, but each unique Country would be its own Sheet. With my data, I would have a ton of sheets.
https://www.excelhow.net/split-data-into-multiple-worksheets-based-on-column.html
I see, the VBA code in this link can be adaptaded to your case
I´ll try to adapt to you, but I´ll need some information:
1-> What´s the name of the column that you want to use to filter the data
2-> What´s the filter for each one of the five sheets you want to use like: (TW = Asia, TH = Asia, NZ = Oceania, etc.). Because those filter´s I will link to each sheet.
To start I think this information can help me. I´ll share with you my file and than you can use to adapt yours
Thanks Eduardo for the response!
I definitely want VBA for this. Here is a snippet of the data:
| Order ID | Shipping Country | Shipping State | Product 1 Qty | Product 2 Qty | Product 3 Qty | Product 4 Qty |
|---|---|---|---|---|---|---|
| 1 | CA | MB | 1 | |||
| 2 | AU | WA | ||||
| 3 | KR | 1 | 2 | 4 | ||
| 4 | JP | JP13 | 2 | 1 |
So basically Order # 1 would be moved to the Canada sheet, Order 2 to the Oceania sheet, Order 3 and 4 moved to the Asia sheet.
Awesome!
I would like to build the filtering/categorization of Country code within the Macro. It's essentially a 2 column table: first column is Country Code, second column is one of the 5 destination sheets. I was going to have to incorporate this table into each Workbook that I have and then create a column next to the original data's Country with a VLOOKUP function referencing this table and then have the Macro run to split the data. I'd like to have this data within the Macro so that I don't have to manually do this for each workbook. I would just have to manually add each Country Code and its Destination Sheet to the Macro and (if things change in the future), I can always control which Country goes to which Destination Sheet.
| Country Code | Destination Sheet |
|---|---|
| CN | Asia |
| JP | Asia |
| AU | Oceania |
etc.
Hi ClayRoss1
My name is Eduardo. I am an independent consultant, first of all thanks for being part of the Microsoft Community, I will try to help you.
Could you share with me your file? You need a VBA Code to do what you want. If you like I can try to do this.
If you were able to share your file you can also send me in the file all the things you need to be done?
I hope this information is useful for you. If you still have questions, answer here so I can continue helping you.
Sincerely,
Eduardo