Split an Excel file into Multiple Sheets Based on a Specific Column

Hi guys,

I need to split a excel file in multiple sheets based on column W. Every sheets must be named based on split criteria.
After that I want to save the resulting sheets in a specific location.
Thx

Some suggestions you may fund useful:

1. Our answerers are looking to "help" more often than they are looking to "do an entire project for you for free".
2. Jump into this project yourself, when you get stuck on ONE specific hurdle, post that ONE item as a forum question.
3. Show us what you've tried in an attached sample workbook and how it's not working for you. When you're doing the work yourself, just writing out a complete example question in a forum often points you to solution before you even finish posting.
4. If you can't make any headway at all and no help is forthcoming in the free forum(s), we do have a Commercial Services forum where you can pay for assistance directly from our EF gurus."

Thank you for your reply.
I can't provide the file, contains some confidential data but I can share my work so far (you will find it below). My approach was to filter the column based on specific criteria, copy filtered cells to another sheet, save that sheet to a specific location, then clear filters and repeat the procedure for another filter criteria.
I am stuck when one of the filter criteria is missing, I don't know how to skip part of the code when a certain filter criteria is missing.
How to skip this part of the code when filter criteria NCRT is missing in column W

Please Login or Register  to view this content.

My entire code

Please Login or Register  to view this content.

I have attached the file.
Thx.

Should it be new files or new sheets?

Please Login or Register  to view this content.

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

If you need to add another filter criteria, simply add it to the array in the code.

Thank you very much Mumps1, It works perfectly I owe you

Originally Posted by Mumps1

You are very welcome.

Hi Mumps1,

I really appreciated your help with my request. I have another question, it is possible, adapting the script from you, to split the resulting files based on the currency?. The currency can be EUR, CHF, USD and will always be in column G (the currency may differ from file to file, in one file it may be only CHF and EUR, in another file it may be CHF, EUR and USD).
The resulting files I would like to be named like this: "INT EUR", "INT CHF" and so on. The files I want to be saved in C:\Users\Home\Desktop\My Folder\Currency. I updated the test file with currency in column G.
THX

Originally Posted by Mumps1

Do you still want to split and save the data based on "NCRT", "INT", "REV" and then split each resulting file based on currency or just split and save on currency without splitting on "NCRT", "INT", "REV"?

I want to split and save the data based on "NCRT", "INT", "REV" and then split each resulting file based on currency.

It is not necessary to quote full messages. You apparently haven't looked at the code in # 8. This is much more efficient and dynamic.

It is not necessary to always create a tab and delete it.

Please Login or Register  to view this content.

Someone with more posts doesn't necessarily write better code

Thanks guys, both solutions provided work very well. I am grateful to you.

@Mumps1,

They seemed unnecessary to me. But of course it can be adjusted.