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.

Related Articles

Create multiple Excel spreadsheets

I am trying to create multiple spreadsheets in a single Excel file and I get the following error: Public member 'workbook' on type 'Application class This is my code: Private Sub MakeExcelWorksheets_Click(sender As Object, e As EventArgs) Handles Mak

How can I create and autowire beans based on the Enum type?

Apologies if my terminology is jumbled up, I'm rather new to Spring. I have inherited an application that has a rather unappealing approach as follows: The app deals with 3 types of data (T1, T2, T3). The types are in Enum class DataType. There is a

How do I create an Excel spreadsheet and a file in iPhone sdk?

i want to generate the application to create the excel file in iPhone sdk. can anybody suggest me to create it.Well, the right guy saw your post! There is an open source C++ library on SourceForge, xlslib, that will do exactly what you need. But wait

How do I create a scala class based on the user input?

I have a use case where I need to create a class based on user input. For example, the user input could be : "(Int,fieldname1) : (String,fieldname2) : .. etc" Then a class has to be created as follows at runtime Class Some { Int fieldname1 Strin

How do you create a transition diagram based on the C code

I have to create transition diagrams for a lexical analyzer for the identifiers and numbers. The code is included below: /* recursive factorial function */ int fact (int x ) { if (x>1) return x * fact (x-1); else return 1; } void main (void) { int x;

How do I create a makefile file based on the build options?

A long time ago, I remember using some Solaris make, and they had an ingenious option that would automatically detect when the compiler options had changed, and rebuild all rules appropriately. For example, suppose I switch from: g++ -O3 to g++ -g Th

Generating an Excel file based on the contents of the folder

Is it possible to create a excel spreadsheet based on a folder tree, including its subdirectories and filenames? Is their a way to do it with excel itself or is there a program out there that makes this possible?If you need to do this just once and n

Create an Excel spreadsheet from an Oracle database

I have a table in a Oracle database. I have to create a complex spreadsheet structure from the Oracle table. I am looking for best approach to achieve this. Can I use SSIS or use some Oracle utilities to create the spreadsheet. Any help would be extr