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

  • Thread starter Thread starter vivek.c9
  • Start date Start date
V

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,
 
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
 
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
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

Can someone help me to do the above via activex script? Thanks in
advance
 
Why can't you add the filters, do the work, and remove the filters?








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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
 
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
 
Back
Top