Seperate data from one sheet to multiple sheets based on a column

[vivek.c9]

Hello,

I have a master worksheet which i populate with all the data for all
departments. I want data in seperate sheets with the worksheet name as
department. Like I want 1 sheet for Harware one for Software and one
for Furniture.

Hardware 339231
Hardware 339235
Hardware 339239
Sofware 339243
Sofware 324022
Sofware 324030
Furniture 339243
Furniture 324022
Furniture 324030

Thanks,

 

[Ron de Bruin]

You can use AutoFilter

Select a cell in your data table and use Data>Filter>AutoFilter to activate AutoFilter.
In Excel 2007 click on the Data tab on the ribbon and then on Filter.
Tip: Shortcut for the English version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in field 1 and choose Harware .

Copy the filter result to a new worksheet:

1) Select the whole data range or if you not have empty rows/columns in the range
you can use Ctrl * to select all data or use F5>Special>Current region>OK with
one cell selected in the data range.
2) Ctrl c or Edit>Copy
3) Insert>Worksheet
4) Ctrl v or Edit>Paste
5) Select the sheet with the filter
6) Press Esc
7) Press Alt d f f or Data>Filter>AutoFilter to turn off AutoFilter in 97-2003.
In Excel 2007 click on the Data tab on the ribbon and then on Filter.

Do the same for the others

Or try this macro
http://www.rondebruin.nl/copy5.htm

 

[Dave Peterson]

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[vivek.c9]

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm










--

Dave Peterson- Hide quoted text -

- Show quoted text -

I have this data coming out of a stored procedure and I want to
automatically populate excel sheets for different deparments with the
sheet name as department. I cannot use filters.

 

[vivek.c9]

I have this data coming out of a stored procedure and I want to
automatically populate excel sheets for different deparments with the
sheet name as department. I cannot use filters.- Hide quoted text -

- Show quoted text -

Can someone help me to do the above via activex script? Thanks in
advance

 

[Dave Peterson]

Why can't you add the filters, do the work, and remove the filters?

 

[vivek.c9]

Why can't you add the filters, do the work, and remove the filters?








--

Dave Peterson- Hide quoted text -

- Show quoted text -

I have to create and send this excel workbook out everyday. It is good
to have it automated. A macro that seperates all departments in
seperate sheets would work too. but the macro should run whenever they
open the workbook

 

[Dave Peterson]

Personally, I wouldn't want a macro like this to run each time I open the
workbook. If I opened the workbook 5 times one day, I wouldn't want it to run
all 5 times.

But if you want you could create a subroutine in a general module that calls the
macro that does the work:

Option Explicit
sub Auto_Open()
call subroutinethatdoestheworknamehere
end sub