Still trying to copy info from one sheet to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two identical sheets. If something is entered into a cell in sheet1, how do I get excel to automatically enter the same value into an identical cell in sheet2 and do this on a cell by cell basis not as a copy of a range of cells? Any help is greatly appreciated.
 
Hi Erik
maybe i'm missing something but enter
='sheet1'!A1
in cell A1 on sheet 2 and copy down. or you may use
=IF('sheet1'!A1<>"",'sheet1'!A1,"")
 
Erik,

Put this code in the Sheet1 worksheet code module

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

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Erik said:
I have two identical sheets. If something is entered into a cell in
Click to expand...
sheet1, how do I get excel to automatically enter the same value into an
identical cell in sheet2 and do this on a cell by cell basis not as a copy
of a range of cells? Any help is greatly appreciated.
 
Frank,
Problem is I have code in both sheets to format the cells color based on the text value in the cell. Using the formula in sheet2, the code doesn't seem to recognize the value and therefore doesn't format the cell. I was thinking there must be a way to modify my existing cell formatting code to do the job or insert an additional sub. A looped routine that looks at each cell in a range individually and copies it to sheet2 maybe. Here is the code I'm using for the formatting:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("B8:B19,D8:D19,F8:F19,H8:H19,J8:J19,L8:L19,N8:N19"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "SSH": Num = 38
Case Is = "SMH": Num = 39
Case Is = "SSO": Num = 28
Case Is = "SKMH": Num = 36
Case Is = "SA": Num = 43
Case Is = "SBC": Num = 45
Case Is = "HC": Num = 32
Case Is = "ADMIN": Num = 54
Case Is = "OC": Num = 15
End Select
'Apply the color
rng.Interior.ColorIndex = Num
rng.Offset(0, 1).Interior.ColorIndex = Num
Next rng
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target,
Range("B8:B19,D8:D19,F8:F19,H8:H19,J8:J19,L8:L19,N8:N19"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "SSH": Num = 38
Case Is = "SMH": Num = 39
Case Is = "SSO": Num = 28
Case Is = "SKMH": Num = 36
Case Is = "SA": Num = 43
Case Is = "SBC": Num = 45
Case Is = "HC": Num = 32
Case Is = "ADMIN": Num = 54
Case Is = "OC": Num = 15
End Select
'Apply the color
rng.Resize(1, 2).Interior.ColorIndex = Num
With Worksheets("Sheet2").Range(Target.Address)
.Value = Target.Value
.Resize(1, 2).Interior.ColorIndex = Num
End With
Next rng
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Erik said:
Frank,
Problem is I have code in both sheets to format the cells color based on
Click to expand...
the text value in the cell. Using the formula in sheet2, the code doesn't
seem to recognize the value and therefore doesn't format the cell. I was
thinking there must be a way to modify my existing cell formatting code to
do the job or insert an additional sub. A looped routine that looks at each
cell in a range individually and copies it to sheet2 maybe. Here is the
code I'm using for the formatting:
 
Back
Top