How to make text entered in a cell on a sheet, appear the same on another sheet

  • Thread starter Thread starter drewmacher
  • Start date Start date
D

drewmacher

Hello. How do you make text entered in a cell, appear in another cel
in another sheet automatically? The cell references would be the same
 
In A1 of Sheet1 you entered "hello world"
In B5 of Sheet2 you have the formula =Sheet1!A1
Cell B1 displays the same text (hello world)
 
I'm not sure I understand what you are asking, but try putting
the following code in the sheet code module for the worksheet
whose values you are going to change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
Application.EnableEvents = False
Worksheets("SHeet2").Range(Target.Address).Value =
Target.Value
Application.EnableEvents = True
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thank you for that. However, using my defined name of the sheet, sa
Due Date, it doesn't reference it. e.g. A1 in Due Date sheet i
12/3/04. In another defined sheet, called say Internal Use, in same A
cell, put =Due Date! A1
 
Chip Pearson wrote
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
Application.EnableEvents = False
Worksheets("SHeet2").Range(Target.Address).Value =
Target.Value
Application.EnableEvents = True
End If
End Sub
Click to expand...

Curious, why are the 'Application.EnableEvents =' lines needed?
 
David,

In this example, the Application.EnableEvents statements are not
required; I put them in out of habit. Since the Change event
procedure changes a cell's value, the Change event is triggered,
which changes a cell's value, which triggers the Change event,
which changes a cell's value, which triggers the Change event,
and so on, ad infinitum. This is called 'recursion'. They are
not required in this procedure because this procedure changes the
value on another sheet, and this change event procedure will not
be triggered.

It is just force of habit to disable events in Change procedures.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Drewmacher:

There is a space in the sheet's name so you need to put in some singl
quotes: 'Due Date'!

Al
 
Chip Pearson wrote
This is called 'recursion'. They are
not required in this procedure because this procedure changes the
value on another sheet, and this change event procedure will not
be triggered.
Click to expand...

I've found they are not required if the value that is changed is in a cell
outside the range of the change event on the same sheet as well.

Anyway, thanks for clearing things up.
 
Back
Top