Using VBA for Filtering, Copying to new spreadsheet

[kolfinna]

Hi all!

I've done some searching, and quickly realized I have no idea how to even look for what I want to do. Nothing really seems to match. Basically, what I want to do is this:

• Start with spreadsheet "Shire.xls"
• search all records matching the unique identifier RING (returns a few thousand results)
• Copy all those records
• Paste over existing data in workbook "Mordor.xls" in spreadsheet "RING - all"

Subequently, I'll have to use the code to pull three other qualifiers and paste individually into their respective worksheets in Mordor. FRODO -> Frodo - All, GANDALF -> Gandalf - All, SAMWISE -> Samwise - All.

Any thoughts? I usually try to take a stab at doing the coding myself, but I'm a little overwhelmed on this one. I tried building a macro, but it choked when I tried to use an autofilter for "RING". My current process involves adding autofilters, searching within the description field of the record (the row has a lot of associated columns) for the qualifier, copy all, and then paste into Mordor.xls. When I looked at what the macro was trying to do, it was a little ridiculous...

Sub MORDOR_RING() 
' 
' MORDOR_RING Macro 
' Pulls from The Shire spreadsheet into RING - All tab
' 
' Keyboard Shortcut: Ctrl+Shift+D 
' 
    Windows("The Shire.xls").Activate 
    Selection.AutoFilter 
    ActiveSheet.Range("$A$1:$P$7863").AutoFilter Field := 11, Criteria1 := Array( _
'... (bunch of garbage for about 20 lines out of 3648 total rows to be copied) 

End Sub

Thanks in advance for all your help.