Changing one cell on one sheet to be the same as another

[Pauljj]

I have a drop down list on a sheet called "Average Stat" when the user selects this cell I want what shows on a sheet called "Charts" to change to the same name. I thought this would be easy but its not proving so. What I have is the following, this code is set up in the "Average Stat" sheet

VAL1CELL is a named range of one cell in the "Charts" sheet

-------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
Range(VAL1CELL) = Range("C2")
Worksheets("Charts").Calculate
Worksheets("Promotions").Calculate
Worksheets("Average Stat").Calculate
End If

 

[VoG]

Try

Range("VAL1CELL").Value = Target.Value

 

[Pauljj]

hi there, thanks for the quick response, I tried that but on that line I got an error message

Method range of object Worksheet failed ?

 

[VoG]

Maybe

Sheets("Chart").Range("VAL1CELL").Value = Target.Value

 

[Pauljj]

Perfect VoG thank you very much, worked a treat