how can i filter data from one sheet and copy to another via formu

[mona]

i am dealing with 30000 line items of data. i would like to populate various
worksheets based on updated data on the "DATA" sheet. Example: Data sheet
has data for 30 different product lines. i would like to select product A
and any related data to product A and move it on a new worksheet, named "A".
Currently i use the filter, cut and paste feature to do this but would like
to know of a faster method.

thanks

[nijunge]

I'm doing the same thing over and over again.

My solution is a simple macro

Then its just to hope that you can figure out macro progamming. start by recording macro it works like a recording of the procedure you use

[psimon@snet.net]

Set this up as a simple example, then once you understand it, modify it
to work on your actual file.

1.) In a new Workbook, create 4 worksheets, and name them "Data", "A",
"B", "C" (obviously without the quotation marks).
2.) In the "Data" worksheet, enter the column heading "Product" in cell
A1 and "Value" in cell B1.
3.) Enter several rows (say 10 to 20 rows) of data under those column
headings by entering A, B or C in the cells in column A under "Product"
(to represent your Product codes) and then entering some random number
next to each in column B under "Value". (The Value you enter in
irrelevant - we're just setting up a simple database example here for
several records of A, B and C products.)
4.) In the "A" worksheet, enter the word "Product" in cells A1 and D1
and word "Value" in cells B1 and E1.
5.) Do the exact same thing in the "B" sheet and "C" sheet.
6.) In sheet "A", enter the letter A in cell A2; in sheet "B", enter
the letter B in cell A2; and in sheet "C", enter the letter C in cell
A2.

What we've done here so far is set up simple Product-Value database
in the "Data" sheet. Sheet "A" will give filtered results for Product
A; sheet "B" will give filtered results for Product B; and sheet "C"
will give filtered results for Product C.

Do Alt+F11 to go to the VBE, insert a module and enter the following
macro (which you can copy and paste right from here):


Sub RunFilter()
Dim DataRange As Range
Set DataRange = Selection.CurrentRegion

'Filter for Product "A"
DataRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("A").Range("A1:B2"), _
CopyToRange:=Sheets("A").Range("D1:E1"), Unique:=False

'Filter for Product "B"
DataRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("B").Range("A1:B2"), _
CopyToRange:=Sheets("B").Range("D1:E1"), Unique:=False

'Filter for Product "C"
DataRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("C").Range("A1:B2"), _
CopyToRange:=Sheets("C").Range("D1:E1"), Unique:=False
End Sub


Now go to the "Data" sheet, click anywhere within the database and run
this macro. Sheets "A", "B" and "C" will now show filtered results for
the corresponding Products. Change some data in the database and/or
add some additional rows of data and run the macro again. The product
sheets will now reflect those changes.

Now how do you modify this macro to work on your actual file?

1.) In each Product sheet of your actual file, create an area where you
will copy all your column heading from your database to be used as a
Criteria area. Then adjust the "CriteriaRange" line in the macro
accordingly. Let's say you have 6 column headings, with the first being
"Product" and decide to start them in column M. Cells M1 through R1
would contain your column headings, and in cell M2 you would enter the
letter A in the "A" sheet, B in the "B" sheet, etc. Then change the
"Range" parameter of the "CriteriaRange" line to read "M1:R2".
2.) The "CopyToRange" line of the macro represents the Extract (or
"output") area. So in each Product sheet, copy those same 6 column
headings to where you want to view the output. Let's say you want
that to start in column A. Cells A1 through F1 would then contain your
6 column headings. Then change the "Range" parameter of the
CopyToRange" line to read "A1:F1".
3.) Once you've got your code adjusted for Sheets("A"), just copy
that code for each of your remaining 29 products remembering to change
the Sheets parameter for each - Sheets("A") for Product A,
Sheets("B") for product B, etc., changing it in both the
"CriteriaRange" lines and the "CopyToRange" lines.

To make things easier for yourself, you can add a macro button
somewhere in Row 1 of the Data sheet to run this macro. If you Freeze
Panes for Row 1, the button will always be visible even if you scroll
down in the database.

REMEMBER: In order for this macro to work properly, you much have your
cellpointer somewhere within the data area of the database before
running the macro. Otherwise, it will bomb.

I hope this helps.
Paul

[marlea]

I came across this post while browsing, and I'm so glad I found it. Paul, your suggestion is really helpful, and the example you provided is so well explained.

Thank you!!

Set this up as a simple example, then once you understand it, modify it
to work on your actual file.

1.) In a new Workbook, create 4 worksheets, and name them "Data", "A",
"B", "C" (obviously without the quotation marks).
2.) In the "Data" worksheet, enter the column heading "Product" in cell
A1 and "Value" in cell B1.
3.) Enter several rows (say 10 to 20 rows) of data under those column
headings by entering A, B or C in the cells in column A under "Product"
(to represent your Product codes) and then entering some random number
next to each in column B under "Value". (The Value you enter in
irrelevant - we're just setting up a simple database example here for
several records of A, B and C products.)
4.) In the "A" worksheet, enter the word "Product" in cells A1 and D1
and word "Value" in cells B1 and E1.
5.) Do the exact same thing in the "B" sheet and "C" sheet.
6.) In sheet "A", enter the letter A in cell A2; in sheet "B", enter
the letter B in cell A2; and in sheet "C", enter the letter C in cell
A2.

What we've done here so far is set up simple Product-Value database
in the "Data" sheet. Sheet "A" will give filtered results for Product
A; sheet "B" will give filtered results for Product B; and sheet "C"
will give filtered results for Product C.

Do Alt+F11 to go to the VBE, insert a module and enter the following
macro (which you can copy and paste right from here):


Sub RunFilter()
Dim DataRange As Range
Set DataRange = Selection.CurrentRegion

'Filter for Product "A"
DataRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("A").Range("A1:B2"), _
CopyToRange:=Sheets("A").Range("D1:E1"), Unique:=False

'Filter for Product "B"
DataRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("B").Range("A1:B2"), _
CopyToRange:=Sheets("B").Range("D1:E1"), Unique:=False

'Filter for Product "C"
DataRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("C").Range("A1:B2"), _
CopyToRange:=Sheets("C").Range("D1:E1"), Unique:=False
End Sub


Now go to the "Data" sheet, click anywhere within the database and run
this macro. Sheets "A", "B" and "C" will now show filtered results for
the corresponding Products. Change some data in the database and/or
add some additional rows of data and run the macro again. The product
sheets will now reflect those changes.

Now how do you modify this macro to work on your actual file?

1.) In each Product sheet of your actual file, create an area where you
will copy all your column heading from your database to be used as a
Criteria area. Then adjust the "CriteriaRange" line in the macro
accordingly. Let's say you have 6 column headings, with the first being
"Product" and decide to start them in column M. Cells M1 through R1
would contain your column headings, and in cell M2 you would enter the
letter A in the "A" sheet, B in the "B" sheet, etc. Then change the
"Range" parameter of the "CriteriaRange" line to read "M1:R2".
2.) The "CopyToRange" line of the macro represents the Extract (or
"output") area. So in each Product sheet, copy those same 6 column
headings to where you want to view the output. Let's say you want
that to start in column A. Cells A1 through F1 would then contain your
6 column headings. Then change the "Range" parameter of the
CopyToRange" line to read "A1:F1".
3.) Once you've got your code adjusted for Sheets("A"), just copy
that code for each of your remaining 29 products remembering to change
the Sheets parameter for each - Sheets("A") for Product A,
Sheets("B") for product B, etc., changing it in both the
"CriteriaRange" lines and the "CopyToRange" lines.

To make things easier for yourself, you can add a macro button
somewhere in Row 1 of the Data sheet to run this macro. If you Freeze
Panes for Row 1, the button will always be visible even if you scroll
down in the database.

REMEMBER: In order for this macro to work properly, you much have your
cellpointer somewhere within the data area of the database before
running the macro. Otherwise, it will bomb.

I hope this helps.
Paul

[Dave Peterson]

You may find something close at:

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

or

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

or maybe Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

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

mona wrote:
>
> i am dealing with 30000 line items of data. i would like to populate various
> worksheets based on updated data on the "DATA" sheet. Example: Data sheet
> has data for 30 different product lines. i would like to select product A
> and any related data to product A and move it on a new worksheet, named "A".
> Currently i use the filter, cut and paste feature to do this but would like
> to know of a faster method.
>
> thanks

--

Dave Peterson