How do I create multiple Excel spreadsheets based on the filtering of a certain column?

advertisements

I have the following data:

Name    Year    Sales
Ratata  2012    10000
Pikachu 2013    20000
Akens   2014    30000
Zubat   2012    40000
Onyx    2014    30000
Snorlax 2014    20000

I would like to create a new worksheet for each of the years described in the table. In this example, I will expect 3 new worksheets - 2012,2013, 2014 - That will contain only the rows with the relevant year. In other words, I'd like to filter the table above so that for each year a new worksheet will be created.

Now, I know how to create multiple worksheets using Pivot Table tools (Analyze - options - show report filter pages) - But I am not interested in a Pivot solution, but to keep my original table structure.

I also read about the advanced filtering method - but it won't help me if I want to create 100 new worksheets, for example.

Is there any automated way to do it? Preferably without VBA (But if there's no other way - VBA is ok)

Thanks in advance.


Ok, this answers the question (VBA solution - tested it and it works great):

https://www.extendoffice.com/documents/excel/1174-excel-split-data-into-multiple-worksheets-based-on-column.html

Big thanks to Slai.