Suivi Route du Rhum

Sub definir_noms()
Dim Cel As Range
Dim Nms As Name
For Each Nms In Names
    Nms.Delete
Next
For Each Cel In Rows(1).SpecialCells(xlCellTypeConstants, 23)
    If Cel.Column = 1 Then
        Range(Cel.Offset(2), Cells(Cells.Rows.Count, 1).End(xlUp)).Name = Replace(Cel, " ", "_")
    Else
        Range(Cel.Offset(2), Cells(Cells.Rows.Count, Cel.Column).End(xlUp)).Name = Replace(Replace(Cel & "_LA", " ", "_"), "-", "_")
        Range(Cel.Offset(2, 1), Cells(Cells.Rows.Count, Cel.Column + 1).End(xlUp)).Name = Replace(Replace(Cel & "_LO", " ", "_"), "-", "_")
    End If
Next Cel
End Sub

 Dim strFormule As String, strNom As String, adr2 As String
 Dim nm As Name
 Dim i As Long, c As Range
 strFormule = "=OFFSET(Adaptation!Adr1,,,COUNTA(Adaptation!Adr2)+1)"
 
 With Sheets("Adaptation")
    For i = 2 To .Range([B2], .Range("IV2").End(xlToLeft)).Columns.Count Step 2
        Set c = Cells(2, i)
        strNom = Replace(c.Offset(-1, c.Column Mod 2 = 1), "-", "_")
        strNom = Replace(strNom, " ", "_") & "_?"
 
        adr2 = c.Address & ":" & .Cells(.Rows.Count, c.Column).Address
        Application.Names.Add Replace(strNom, "?", "LA"), Replace(Replace(strFormule, "Adr1", c.Address), "Adr2", adr2)
        Set c = c.Offset(, 1)
        adr2 = c.Address & ":" & .Cells(.Rows.Count, c.Column).Address
        Application.Names.Add Replace(strNom, "?", "LO"), Replace(Replace(strFormule, "Adr1", c.Address), "Adr2", adr2)
    Next
 End With
End Sub

Sub RecupNoms()
    Dim cBateau As Range
    Dim i As Integer
    Dim nom_LA As String, nom_LO As String
    With Sheets("Adaptation")
        'Parcourir les cellules d'entête 2 à 2 pour récupérer en une seule fois les noms Excel des plages
        'de bateau
        For i = 2 To .Range([B2], .Range("IV2").End(xlToLeft)).Columns.Count Step 2
            Set cBateau = .Cells(1, i)
            'Récupération des noms
            nom_LA = Replace(Replace(cBateau.Value, "-", "_"), " ", "_") & "_LA"
            nom_LO = Replace(nom_LA, "_LA", "_LO")
        Next
    End With
End Sub

        ActiveSheet.ChartObjects("Graphique2").Activate
        s = ActiveChart.SeriesCollection.Count
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(s + 1).XValues = "=RR.xls!" & [COLOR=red][B]nom_LO
[/B][/COLOR]        ActiveChart.SeriesCollection(s + 1).Values = "=RR.xls!" & [COLOR=red][B]nom_LA
[/B][/COLOR]        ActiveChart.SeriesCollection(s + 1).Name = "=Adaptation!" & [COLOR=red][B]cBateau.Address
[/B][/COLOR]

   nom_LA = Replace(Replace([COLOR=red][B]NomORIGINAL[/B][/COLOR], "-", "_"), " ", "_") & "_LA"
            nom_LO = Replace(nom_LA, "_LA", "_LO")

        With ActiveSheet.ChartObjects("Graphique2").Chart.SeriesCollection.NewSeries()
            .XValues = "=" & nom_LO
            .Values = "="  & nom_LA
            .Name = "=" & cBateau.Address
        End With

Sub Sailing2()
'Formule de nommage des colonnes de données de chaque bateau choisit
'seule 'Ligne' sera variable et modifié par la boucle sur toutes les cellules
'de chaque colonne, calculera ainsi la hauteur de la plage
    Const strFormule As String = "=OFFSET(Choix!?,2,0,Ligne-3,1)"
    Dim nm As Name, oChart As Chart, s As Series
    Dim c As Range
    Dim nmLA As String, nmLO As String, bateau As String
    Dim i As Long, derLig As Long, j As Integer
    With Sheets("Choix")
        Set oChart = .ChartObjects("Routes").Chart
        For Each s In oChart.SeriesCollection
            s.Delete
        Next
        For Each nm In Names
            If nm.Name <> "Ligne" Then nm.Delete
        Next
        Application.Names.Add "Ligne", 4
 
        For i = 16 To .Range("IV2").End(xlToLeft).Column Step 2
            Set c = .Cells(1, i)
 
            'Dernière ligne à parcourir?
            If .Cells(.Rows.Count, c.Column).End(xlUp).Row > derLig Then derLig = .Cells(.Rows.Count, c.Column).End(xlUp).Row
 
            'Le nom du bateau
            bateau = .Cells(1, i).Offset(, .Cells(1, i).Column Mod 2 = 1)
 
            'Les noms pour le calcul
            strNom = Replace(Replace(bateau, "-", "_"), " ", "_")
            nmLA = strNom & "_LA"
            nmLO = strNom & "_LO"
 
            Application.Names.Add nmLA, Replace(strFormule, "?", c.Address)
            Application.Names.Add nmLO, Replace(strFormule, "?", c.Offset(, 1).Address)
            'Ajout des series avec pour source de données les plages nommées
            j = oChart.SeriesCollection.Count
            oChart.SeriesCollection.NewSeries
            oChart.SeriesCollection(j + 1).Name = bateau
            oChart.SeriesCollection(j + 1).XValues = "=" & ThisWorkbook.Name & "!" & nmLO
            oChart.SeriesCollection(j + 1).Values = "=" & ThisWorkbook.Name & "!" & nmLA
        Next i
 
        'Parcours des lignes de 5 à derLig
        For i = 5 To derLig
            Application.Names("Ligne").RefersTo = "=" & i
            Calculate
        Next i
    End With
End Sub