Your question is very timely. I encountered a similar issue just earlier
today!
Check out this code and see if it (pretty much) does what you need:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim custLookup As Range, lRow As Long
Dim i As Range
If Target.Cells.Count 1 Or _
Target.Value = "" Then Exit Sub
With Sheets(2)
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Select Case Target.Column
Case Is = 1
Set custLookup = Sheets(2).Columns("A").Find( _
What:=Target.Value, _
LookIn:=xlValues, _
MatchCase:=False)
Set rng = Range("A1", Cells(lRow, 1))
For Each i In rng
If custLookup Is Nothing Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
Else
If MsgBox("This customer already exists. " & _
"Would you like to add them again?", _
vbYesNo) = vbYes Then
i.EntireRow.Copy Sheets(2).Cells(lRow + 1, 1)
End If
End If
Next i
Case Is = 2
' do stuff
End Select
End Sub

Please notice, since this is a private sub, you have to right-click on a
sheet's tab, click View Code, and paste it into the window that opens. It
should run automatically when you activate a cell in Column A.


Regards,
Ryan--


--
RyGuy


"Southern Boy" wrote:

I have created a 3sheet work book which auto sums all my values required. I
need to be able to offer on the 1st work sheet the option to change text in a
cell and for that text to be mapped to the folloing 2 work sheets.
Example these are room names which could change according to project, butall
other items are fine.