Look for value in one column & copy value from another

[ipbr21054]

Hi,
Looking for some advice please to extract some info from my worksheet like below.
The 2 columns in mind are search column D & datate stored in column B

So the code will look in column D for GOLD WING USA & also GOLD WING UK
Once either of the above is found copy the value from column B to column M or O

See screenshot.
Working down my worksheet starting at Row 8
GOLD WING USA will copy the value in the cell that is filled Blue to column M

GOLD WING UK will copy the value in the cell that is filled Red to column O

GOLD WING UK will copy the value in the cell that is filled Green to the next empty cell in column O

GOLD WING UK will copy the value in the cell that is filled White to the next empty cell in column O

 

[Micron]

If one can look for LIKE ("Gold Wing *) it would be slightly easier I think. However, if you want people to work with data like that you might get help faster if you posted data - at least a copy/paste from your sheet would be better than nothing. Today's our Thanksgiving and I have company for dinner so don't have time to start from scratch.

 

[ipbr21054]

Hello,
Basically I’m looking to only copy values of GOLD WING of which there are two possibilities,
UK & USA that’s it.

My goal is a list of UK & USA values.
Whether it be in another column or anything really.

 

[ipbr21054]

Morning,
Im playing with the following just to see how it will work but when i run the command button nothing happens at all.
I dont even get an error.

Below im just trying to get the search / find - copy / paste side working then look into just copying the value from the cell in column D as opposed the whole row

Private Sub GoldWingCountry_Click()
Dim Cell As Range
Dim ws As Worksheet
Set ws = Sheets("MCLIST")

With Sheets("MCLIST")
      For Each Cell In .Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
        If Cell.Value = "UK" Then
            .Rows(Cell.Row).Copy Destination:=Sheets("COUNTRYLIST").Rows(Cell.Row)
        End If
    Next Cell
End With
End Sub

Nothing fancy but trying to get it like so,see screenshot

 

[Georgiboy]

Maybe this:

Private Sub GoldWingCountry_Click()
    Dim Cell As Range
    Dim wsMC As Worksheet
    Dim wsCountry As Worksheet
    
    Set wsMC = Sheets("MCLIST")
    Set wsCountry = Sheets("COUNTRYLIST")
    
    wsCountry.Range("A1") = "GOLD WING UK"
    wsCountry.Range("B1") = "GOLD WING USA"
    
    With wsMC
        For Each Cell In .Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            If Cell.Value = "GOLD WING UK" Then
                Cell.Offset(, -2).Copy wsCountry.Range("A" & Rows.Count).End(xlUp).Offset(1)
            ElseIf Cell.Value = "GOLD WING USA" Then
                Cell.Offset(, -2).Copy wsCountry.Range("B" & Rows.Count).End(xlUp).Offset(1)
            End If
        Next Cell
        Application.CutCopyMode = False
    End With
End Sub

Or without copy/ paste

Private Sub GoldWingCountry_Click()
    Dim Cell As Range
    Dim wsMC As Worksheet
    Dim wsCountry As Worksheet
    
    Set wsMC = Sheets("MCLIST")
    Set wsCountry = Sheets("COUNTRYLIST")
    
    wsCountry.Range("A1") = "GOLD WING UK"
    wsCountry.Range("B1") = "GOLD WING USA"
    
    With wsMC
        For Each Cell In .Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            If Cell.Value = "GOLD WING UK" Then
                wsCountry.Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, -2)
            ElseIf Cell.Value = "GOLD WING USA" Then
                wsCountry.Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, -2)
            End If
        Next Cell
    End With
End Sub

 

[ipbr21054]

Many Thanks,
I have used the following from which you kindly advised.

You will see in Red that i added to clear contents from the sheet where values are pasted.
I see when the copy takes place it also takes the background color & also the border lines so on the sheet COUNTYRYLIST not only does the values get pasted but also the background cell color & border lines.

Can you edit the code & show me the added code that i hope you can help with.

Either ONLY copy values & NOT background color / border lines OR when the Range A2;B500 has the contents cleared then also remove background color & border lines.

Private Sub GoldWingCountry_Click()
    Dim Cell As Range
    Dim wsMC As Worksheet
    Dim wsCountry As Worksheet
       
    Set wsMC = Sheets("MCLIST")
    Set wsCountry = Sheets("COUNTRYLIST")
     
    With Sheets("COUNTRYLIST")
        .Range("A2:B500").ClearContents
    End With
        
    wsCountry.Range("A1") = "GOLD WING UK"
    wsCountry.Range("B1") = "GOLD WING USA"
    
    With wsMC
        For Each Cell In .Range("D8:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            If Cell.Value = "GOLD WING UK" Then
                Cell.Offset(, -2).Copy wsCountry.Range("A" & Rows.Count).End(xlUp).Offset(1)
            ElseIf Cell.Value = "GOLD WING USA" Then
                Cell.Offset(, -2).Copy wsCountry.Range("B" & Rows.Count).End(xlUp).Offset(1)
            End If
        Next Cell
        Application.CutCopyMode = False
    End With
   Sheets("COUNTRYLIST").Select
End Sub

 

[Georgiboy]

Did you try the second piece of code from post 5?

That is values only.