Dim graph As Boolean 'mémorise la variable
Sub Graphiques()
'se lance par les touches Ctrl+G
graph = True: Moyenne: graph = False
End Sub
Sub Moyenne()
'se lance par les touches Ctrl+M
Dim P As Range, rc&, cc%, col%
Set P = Union(Columns(1), Selection.EntireColumn)
Application.ScreenUpdating = False
'---copier-coller---
With Workbooks.Add(xlWBATWorksheet).Sheets(1) 'nouveau document
P.Copy .[A1]
.[A1].Copy .[A1] 'allège la mémoire
Set P = .UsedRange
rc = P.Rows.Count: cc = P.Columns.Count
If rc = 1 Or cc = 1 Then .Parent.Close False: Exit Sub 'sécurité
.Name = "Moyenne"
End With
'---colonnes Real time et Moyenne---
With P(1, cc + 1)
.Resize(, 2) = Array("Real time", "Moyenne")
.Resize(, 2).Font.Bold = True
.Resize(, 2).Font.ColorIndex = 3 'rouge
.Cells(2, 2).Resize(rc - 1) = "=AVERAGE(RC2:RC[-2])"
.Cells(2).Resize(rc - 1) = "=RC1-INDEX(C1,MATCH(MAX(C[1]),C[1],0))"
End With
'---graphique(s)---
With P.Parent.ChartObjects.Add(0, 50, ActiveWindow.VisibleRange.Width, 400).Chart
.ChartType = xlXYScatterLinesNoMarkers
.SetSourceData P(1, cc + 1).Resize(rc, 2)
End With
If graph Then
For col = 2 To cc
If P(1, col) <> "" Then
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = P(1, col)
P.Columns(1).Copy Cells(1)
P.Columns(col).Copy Cells(1, 3)
Cells(2, 2).Resize(rc - 1) = "=RC1-INDEX(C1,MATCH(MAX(C[1]),C[1],0))"
Cells(1, 2) = "Real time"
Cells(1, 2).Font.Bold = True
With ActiveSheet.ChartObjects.Add(0, 50, ActiveWindow.VisibleRange.Width, 400).Chart
.ChartType = xlXYScatterLinesNoMarkers
.SetSourceData Cells(1, 2).Resize(rc, 2)
End With
End If
Next
P.Parent.Activate
End If
Application.ScreenUpdating = True
End Sub