Help to modify VBA script so it displays results in other sheet ce

[Guest]

Hi!

I have this script which reads my choice from a drop down list and displays
the choice in another cell. How can I modify it so it display the result in a
cell in another worksheet in the document?

script:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range

Set myRng = Me.Range("I13")

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, myRng) Is Nothing Then Exit Sub
If .Value = "" Then Exit Sub

Select Case LCase(.Address(0, 0))
Case Is = "i13"
If LCase(.Value) = LCase("click here to choose doortype") Then
'skipit
Else
Application.EnableEvents = False
Me.Range("G14").Value = Me.Range("G14").Value & .Value
..ClearContents
Application.EnableEvents = True
End If
.....

 

[Simon Lloyd]

you just need to change where it says Me.Range("G14") to
Worksheets("Sheet2").Range("A1") that should display your result in
Cell A1 on Sheet2.

It seems to be that simple, its just that the code you have adds the
contents of I13 to the destination cell without any spaces so you end
up having a long string of letters instead of clear words try this
change to the line
Me.Range("G14").Value = Me.Range("G14").Value & " " &
..Value.ClearContents
you also have a couple of ommissions in your code so below is the code
that works for Cell G14 with spaces between the words!

regards,
Simon

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Me.Range("I13")

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, myRng) Is Nothing Then Exit Sub
If .Value = "" Then Exit Sub

Select Case LCase(.Address(0, 0))
Case Is = "i13"
End Select
If LCase(.Value) = LCase("click here to choose doortype") Then
'skipit
Else
Application.EnableEvents = False
Me.Range("G14").Value = Me.Range("G14").Value & " " &
..Value.ClearContents
Application.EnableEvents = True
End If
End With
End Sub

 

[Guest]

Thanks! It worked great.