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

Still Learning wrote:

This is a small problem I'm just giving it a very detailed explanation

Here's what I have:
I'm working on a macro that I will need to use every month. The data is
always in the same format/nomenclature but I want to filter out some data
that I don't need in my analysis. Each piece of data is on a seperate row
and I want to filter it by column "A." Like lets say some rows in column A
say "duck" some say "goose" and some say "dog." When I get the data the rows
are all mixed up like this:

A B
duck blah blah blah other data in other cells
duck blah blah blah other data in other cells
goose blah blah blah other data in other cells
duck blah blah blah other data in other cells
dog blah blah blah other data in other cells

Here's what I need to do:
In one step of my macro I want to be able to sort or filter the data and
copy and paste for instance just the rows starting with "duck" into another
sheet.

Here's my problem:
When I try to record a macro that does this I've tried using autofilters and
when I press ctrl down it records in the macro what cell it references
(instead of just always referring to where the data ends, after I've filtered
to just show the data I want to copy and paste).

If this doesn't make sense drop a note and I'll explain better

--

Dave Peterson