Input Value to Multiple Cells

C

Chris

I have two worksheets contained in the same workbook that
I would like to behave as a single cell value. For
instance, if a value is input into cell A1 on Sheet 1, I
would like it to automatically be entered into cell A1 on
Sheet 2, and vice versa. This can be accomplished with a
handful of if-then statements but is there a more elegant
solution such as cell naming?

Thanks.
 
D

Don Guillett

simply use the control key and select the tabs desired to select those
sheets>now enter what you want in one and it will be the same in the
others>select one tab to undo the multiple selection.
 
P

pfsardella

1. Click on Sheet2, Cell A1. Enter an '=' sign.
2. Click on Sheet1, Cell A1. Hit <Enter>

Everytime you change the value on Sheet1, the value on Sheet2 will
change.

HTH
Paul
 
G

Guest

Sorry, I should have specified. This is for an
application, not just a one-time entry. I would like it
to function in that way but transparent to the user.

Chris
 
D

Doug Glancy

Chris,

This would work for range A1 in Sheet1 and Sheet2. Paste it into the
ThisWorkbook code module in the VBA (not a regular code module):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name = "Sheet1" Then
If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
Worksheets("Sheet2").Range("A1") = Sh.Range("A1")
End If
ElseIf Sh.Name = "Sheet2" Then
If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
Worksheets("Sheet1").Range("A1") = Sh.Range("A1")
End If
End If

End Sub

hth,

Doug
 
W

Walt Weber

Hi Chris,

Try this if you want the cell values to be on Sheet2.
Enter it on the Sheet1 object in the VBA editor.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet2"). _
Range(Target.Address).Value = Target.Value
End Sub

OOPs - "and vice versa"
Enter this on the Sheet2 object:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet1"). _
Range(Target.Address).Value = Target.Value
End Sub

Best Regards,
Walt
 
Top