Copy to Filter search criteria

[jrott]

I have 2 spreadsheets, both have customer ship-to names in them, but they aren't an exact match. I have tried vlookup, index and match etc. to pull the data I need from sheet 1 into sheet 2 , but none will get me the information I need correctly. I am able to copy the name data information from a cell on sheet 2 and paste it into the drop down filter search bar on the name column in sheet 1 and then select the information that comes up in the filtered selection of sheet one and copy and paste it back by hand into the correct columns in sheet two. The problem I am facing is that I would have to do this several thousand times and it doesn't seem practical. I have been trying to find a vba example I could use, but after extensive searching I can't find a solution that addresses this. Any suggestions? This is excel 2010. Thanks for your help in advance, Jeff

 

[jrott]

So, I tried recording a macro. Here is the code I am having issues with that I could use a VBA guru's help on. I would normally trial and error this until I found all the right code items, however I am on a bit of a time crunch and need to just let an expert help me. Thank you again for any help you can provide. jrott

Sub Macro5()
'
' Macro5 Macro
'


'
    Range("K425").Select [B]'How do I get it to a genral reference versus a specfic cell?[/B]
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet 1").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=14, Criteria1:= _
        "[B]=I NEED THIS TO REFERENCE WHATEVER HAS BEEN COPIED and change as it loops through[/B]"
    Sheets("MySheet Name").Select
    Range("M1564:N1564").Select [B]' This should copy the two columns referenced that come up after the data is filtered[/B]
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet 2").Select
    Range("I425").Select[B] 'Pick the next row in line to paste info to. [/B]
    ActiveSheet.Paste  '[B]Sheet2[/B]

[B]'THis would need to loop through the data that needs to be filled in.[/B] 
End Sub

 

[jrott]

Bumping to see if someone can help me with this issue?