- Code: Select all
Dim sourcechart As Range
Dim z As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim UserWants As Range
Dim chartarea As Range
Dim x As Range
Set UserWants = ActiveCell
Set chartarea = Sheets("Sheet2").Range("A" & UserWants.Row)
Set x = chartarea.End(xlToRight)
Dim cht As Excel.Chart
Set cht = Charts.Add
Set sourcechart = Sheets("Sheet2").Range(chartarea.Address, (Cells(UserWants.Row, x.Column).Address))
z = sourcechart.Address
With cht
.ChartType = xlLine
.SetSourceData Source:=sourcechart, PlotBy:= _
xlRows
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow
Dim CopyingTo As Range
Static x As Range
Set lastrow = Sheets("Sheet2").Range("A65536").End(xlUp)
Set CopyingTo = Sheets("Sheet2").Range("A5:A" & lastrow.Row)
Range(Range("F5"), Range("F65536").End(xlUp)).Copy _
Destination:=CopyingTo.End(xlToRight).Offset(0, 1)
If CopyingTo.End(xlToRight).Offset(0, 1).Column > 120 Then
CopyingTo.Offset(0, 1).Delete Shift:=xlToLeft
End If
If ActiveSheet.ChartObjects.Count > 0 Then
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(z), PlotBy:= _
xlRows
End If
End Sub
Just alter the column > 120 to whatever you need
note - a fast refresh rate will allow ver little detail to display. maybe only a few seconds worth.