Input Value to Multiple Cells

  • Thread starter Thread starter Chris
  • Start date Start date
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.
 
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.
 
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
 
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
 
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
 
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
 
Back
Top