Copy row and paste to specified sheet if 2 terms are met

[munchers65]

I am trying to get my Excel sheet to search a specific column for text, if it finds the correct text I would like for it to search a second column for more specific text. If the text is found in both columns I would like the row it was found in to be copied to a specified sheet. I have a ton of tabs so I am unsure if I can have it look in a range of tabs or if I have to specify each tab in each Macro. The first sheet is names Jun 2016 and I want it to search column B for Amazon and then search column G for Vendor. If found in both columns I would like it to copy the row it was found in to the Amazon tab for tracking. Please let me know if this is not clear enough. I am not super familiar with Macros but if someone could help walk me through this I think I could do it. Thank you!

 

[mrmmickle1]

Hey munchers65! Welcome to the messageboard. Perhaps something like this will get you started:

[COLOR=#0000ff] Option Explicit[/COLOR]
[COLOR=#0000ff] Sub [/COLOR]Test()
 
 [COLOR=#0000ff]   Dim [/COLOR]AmazonCll [COLOR=#0000ff]As[/COLOR] Range
  [COLOR=#0000ff]  Dim [/COLOR]ws [COLOR=#0000ff]As [/COLOR]Worksheet
[COLOR=#0000ff]    Dim[/COLOR] strFirstFound [COLOR=#0000ff]As String[/COLOR]


 [COLOR=#0000ff]   Set [/COLOR]ws = Sheets("Sheet1")[COLOR=#008000] 'Change Accordingly[/COLOR]
 [COLOR=#0000ff]   Set [/COLOR]AmazonCll = ws.Columns(2).Find("Amazon", , xlValues, xlPart, xlByRows, xlNext, False) [COLOR=#008000] 'Search Column B for Amazon[/COLOR]
   [COLOR=#0000ff] If [/COLOR]AmazonCll[COLOR=#0000ff] Is Nothing Then Exit Sub[/COLOR] [COLOR=#008000]'If Amazon is not found exit procedure[/COLOR]
    strFirstFound = AmazonCll.Address
    
[COLOR=#0000ff]    Do[/COLOR]
[COLOR=#0000ff]        If[/COLOR] AmazonCll.Offset(, 5).Value = "Vendor" [COLOR=#0000ff]Then[/COLOR]
            AmazonCll.EntireRow.Copy Sheets("Amazon").Rows(Cells(Rows.Count, "A").End(xlUp).Row + 1) [COLOR=#008000]'Copy and Paste Data[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]


[COLOR=#0000ff]     Set [/COLOR]AmazonCll = ws.Columns(2).FindNext(After:=AmazonCll)
 [COLOR=#0000ff]    Loop Until[/COLOR] AmazonCll.Address = strFirstFound [COLOR=#008000]'Loop through all Amazon Values in Column B until last row[/COLOR]
     MsgBox "Done!"
[COLOR=#0000ff] End Sub[/COLOR]

 

[munchers65]

Please excuse me if I am missing something simple but I am getting Error code 9 on the below line.

Set ws = Sheets("Sheet1") 'Change Accordingly

Should I be changing the Sheets or the Sheet1 to the name of the sheet, or both?

Set ws = Amazon("Amazon") 'Change Accordingly

 

[munchers65]

Well I feel silly so I understand it should be the below text however I do not see the point that it is looking in JUN 2016 for Amazon.
Set ws = Sheets("Amazon") 'Change Accordingly