Find a cell and replace contents

[trogtrog]

I'm a little out of my depth with a task I'm trying to do in Excel and would appreciate help!

What I'm trying to do, when on sheet A, is create a macro to find a cell on another sheet and replace it's contents with the contents of a cell on sheet A.

For example, on sheet A

Cell A1 has the name which corresponds to one other the other sheets...let's say that the name is 'Biology' which corresponds to a sheet in the same workbook called 'Biology'
Cell A2 has a unique number which will be found somewhere in column A of the other sheet...let's say the number is 310008 which will be found on Row 12, i.e. in cell A12 of sheet Biology
Cell A3 has a manually entered value which, when the macro is triggered (button press), I'd like to find the approriate cell, which will be in the same row as the looked-up value, in column E, and 'paste' the contents of sheet A, Cell A3 into the looked-up cell, which in this example is sheet Biology, cell E12.

Can anyone help please?

 

[DanteAmor]

Hi and welcome to teh forum!

Put the following code in the events of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "[COLOR=#ff0000]A3[/COLOR]" Then
    Dim sh As Worksheet, f As Range, sh1 As Worksheet
    If Target.Count > 1 Then Exit Sub
    If Range("[COLOR=#ff0000]A1[/COLOR]").Value = "" Or Range("[COLOR=#ff0000]A2[/COLOR]").Value = "" Then Exit Sub
    For Each sh In Sheets
      If LCase(sh.Name) = LCase(Range("[COLOR=#ff0000]A1[/COLOR]").Value) Then
        Set sh1 = sh
        Exit For
      End If
    Next
    If Not sh1 Is Nothing Then
      Set f = Sheets(Range("[COLOR=#ff0000]A1[/COLOR]").Value).Range("[COLOR=#ff0000]A:A[/COLOR]").Find(Range("[COLOR=#ff0000]A2[/COLOR]").Value, , xlValues, xlWhole)
      If Not f Is Nothing Then
        f.Offset[COLOR=#ff0000](, 4)[/COLOR].Value = Target.Value
        MsgBox "Updated Value"
      Else
        MsgBox "The value does not exist"
      End If
    Else
      MsgBox "The sheet does not exist"
    End If
  End If
End Sub

-----------------------------------
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

-----------------------------------

Go back to the sheet and type something in cell A3, the other sheet will be updated automatically.

 

[trogtrog]

Wow, thanks for the rapid reply, that has worked a treat

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.