Creating automatic chart using other sheet's data?

[trigger]

I'm trying to create a chart using other sheet's data.


I have data sheet(sheet2) and I want to create chart in sheet1
Cell names(London, Seoul, Tokyo, LA) in sheet1 are same in sheet2.
https://blog.naver.com/ysyeon/221335816516


I want to generate automatically changing chart in sheet1 using data in sheet2 by clicking cell name in sheet1.


When I tried automatic chart which has data&chart in the same sheet, I used this code and it worked.
How can I solve this?



Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim rngF As Range, rngC As Range, rngSource As Range
Dim strTxt As String

Set rngF = [T1:AW1]
If Target.Count > 1 Then
Exit Sub
End If

If Not Intersect([C21:C1209], Target) Is Nothing Then
With ActiveSheet.ChartObjects(1)
strTxt = Target.Value & "export data(US$)"
Set rngC = Target.Offset(, 17).Resize(, 30)
Set rngSource = Union(rngF, rngC)
.Chart.SetSourceData Source:=rngSource, PlotBy:=xlRows
End With
End If

Set rngF = Nothing
Set rngC = Nothing
Set rngSource = Nothing

End Sub

[trigger]

I figured out.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim rngF As Range
Dim rngC As Range
Dim rngSource As Range
Dim strTxt As String
Dim TgtRow As Long
If Target.Count > 1 Then
Exit Sub
End If
If Not Intersect(Range("B12:B182"), Target) Is Nothing Then
TgtRow = Target.Row - 10
With Sheet2
Set rngF = .Range("$B$1:$G$1")
Set rngC = .Range("B" & TgtRow & ":G" & TgtRow)
Set rngSource = Union(rngF, rngC)
End With
ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=rngSource, PlotBy:=xlRows
Set rngF = Nothing
Set rngC = Nothing
Set rngSource = Nothing
End If
End Sub