Extracting Data based on user input

**gr8cobbler** · 07-29-2010, 07:29 PM

Thanks for taking a look.

I'm in k12 education and I have a workbook called TestMaster that automatically collects test data from a bubble reader over the course of a year. Data arrives in sets of roughly 25 unique records which have a consistent test name as a key field. Users need to be able to call up these class sets of records at will for review. We can assume the TestMaster database needs to be sorted by test name prior to processing.

A second workbook called teacher template has a list of all test names available from a drop box and a button tied to a macro to “Get My Tests”

The desired outcome is to be able to have the teacher select a test from the list of test names and then press the button to sort and then extract all the records that match the test requested and place that data on another sheet in the teacher template workbook. My goal is to eliminate as many steps as I can to make this easy for the teacher

The data extracted is then processed in the second workbook providing charts and graphs of student progress but I have that in the bag already.

I've done some programming and have made some cool macros already but I’m a little stumped by this one. How would I best go about extracting the record set from the TestMaster worksheet using the known test name and placing it in a worksheet in the Teacher Template file?

**shg** · 07-29-2010, 07:59 PM

Why not jut use AutoFilter on the TestMaster workbook?

**gr8cobbler** · 07-29-2010, 08:08 PM

I need to further process the data, I lose a lot of the teachers if there are too many steps. Maybe I don't know enough about using autofilter for extracting data. Eager to learn though..,......

**shg** · 07-29-2010, 08:18 PM

Select A1, Data > Filter > AutoFilter, and select a test number from the drop-down in col A

**gr8cobbler** · 07-29-2010, 08:26 PM

That much I can do,

can you write a macro that can filter for that , then copy matches to a differnt workbook? I'm having problems with that.

**MarvinP** · 07-29-2010, 08:46 PM

Auto Filters are a great feature you should use. I have created an Advanced Filter on Sheet 2 that may be useful also. Take a look at the attached. Also the newer version 2010 of Excel has some useful new features that make this stuff easier.

**gr8cobbler** · 07-29-2010, 10:32 PM

Thanks, I'm sure you are correct. The syntax is losing me a bit I think, I could follow your code pretty well. As a district we are transitioning to 2010 this year though everyone currently has 2003. Makes it a bit harder. I am impressed with 2010 and can't wait until it is our standard.

Anyplace you'd send me to learn how to navigate the syntax? I'm just worrying my way through it with trial and error, change it and see what happens....

Thanks for the assistance.