Auto Copy Text from one work sheet to another

[Southern Boy]

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.

 

[ryguy7272]

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--

 

[Gord Dibben]

Sheet1 contains a room number in A1

Sheet2 and Sheet3 A1 contain this formula

=Sheet1!A1

Change value in Sheet1 A1 and the others will change also.

This is known as "linking".

More can be found in Help


Gord Dibben MS Excel MVP

On Thu, 10 Jan 2008 09:17:04 -0800, Southern Boy <Southern