Run a search and replace based on what is in another Cell?

[cekenney]

Basically I have two sheets.

Sheet 1 changes monthly. It has a list of names in column A and their associated emails in column B. New Names and Emails are added and taken off and existing emails change.
Sheet 2 comes in daily and has just the list of names.

I need to figure out a macro that will look at sheet 1, look at a cell in column A, take the name that is in that cell.
Search for that name in Sheet 2, then replace that name in sheet 2 with the email(column B) from sheet 1 associated with that name.


It's quite all right if I need to merge the two sheets to accomplish this, but it would be preferable to keep them separate.

[rvasquez]

Hello there,

When do you want this action to take place? On change of a cell in worksheet 1, on add new worksheet?, on button press?

Thanks!

[cekenney]

Really just when the macro is run. So on a shortcut key press or a button would work as well.

[Bishonen]

very bad code, but it works ^^

Sub copyto()

On Error Resume Next

Dim Cell As Range, Ncell As String, dest As Range

Workbooks("From").Activate

For Each Cell In Range("A1", Cells(Rows.Count, 1).End(xlUp))

    Cell.Activate
    
        Cell.Offset(0, 1).Copy
    
        Ncell = Cell.Value
        
        Workbooks("pastto").Activate
    
        Set dest = Cells.Find(What:=Ncell, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
         
        dest.PasteSpecial xlPasteValues
        
        Workbooks("From").Activate
        
            
    Next Cell

End Sub

[cekenney]

very bad code, but it works ^^

Sub copyto()

On Error Resume Next

Dim Cell As Range, Ncell As String, dest As Range

Workbooks("From").Activate

For Each Cell In Range("A1", Cells(Rows.Count, 1).End(xlUp))

    Cell.Activate
    
        Cell.Offset(0, 1).Copy
    
        Ncell = Cell.Value
        
        Workbooks("pastto").Activate
    
        Set dest = Cells.Find(What:=Ncell, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
         
        dest.PasteSpecial xlPasteValues
        
        Workbooks("From").Activate
        
            
    Next Cell

End Sub

How would I change this if I simply wanted to do it from 2 different sheets within the same workbook?

[Bishonen]

How would I change this if I simply wanted to do it from 2 different sheets within the same workbook?

Sub copyto()

On Error Resume Next

Dim Cell As Range, Ncell As String, dest As Range

Worksheets(1).Activate 'chose your "from" worksheet

For Each Cell In Range("A1", Cells(Rows.Count, 1).End(xlUp))

    Cell.Activate
    
        Cell.Offset(0, 1).Copy
    
        Ncell = Cell.Value
        
        Worksheets(2).Activate 'chose your destination worksheet
    
        Set dest = Cells.Find(What:=Ncell, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
         
        dest.PasteSpecial xlPasteValues
        
        Worksheets(1).Activate 'chose your "from" worksheet again
        
            
    Next Cell

End Sub

[cekenney]

wow! worked like a charm thanks so much!