Excel > autogenerate graph

I have written a couple of macros which more or less work, but not automatically. The result (a graph) is okay, but the data used to create this graph must be selected based on what the user types in a textbox of a userform.

Sub Analyse_Einzeln()
' this macro doesn't look up the SupplierNr
' this macro doesn't take into account the number of Periods the user
' wants to see... I manually selected the data :(
        ' in this example
        ' Sheet ArtikelData contains the imported TXT files
        ' row 2 contains the SupplierNr
        ' row 4 contains the Amount
        ' row 5 contains the Purchasing_Value
        ' row 7 contains the Intermediary_Value
        ' row 8 contains the EndConsumer_Value
        '
        ' however, in the REAL macro, these rows should depend on
        ' the last column that is filled with data
        ' the row in which the SupplierNr is found
        ' Row 4 would be Row(SupplierNr)+2
        ' Row 5 would be Row(SupplierNr)+3
        ' so
        ' instead of B4:E7, the selection should be 'LastColumn-Period''RowSupplierNr+2:'LastColumn''RowSupplierNr+5'
        ' where LastColumn is the last column that is filled in the ArtData sheet
           ' this can be checked by looking in row 1, where the dates that the TXT file is imported are located
        ' where RowSupplierNr is the row in which the specified SupplierNr is found
        ' where Period is the number of periods specified in the form by the user
        ' info from Userform: SupplierNr and #Periods

    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("ImportData").Range("B4:E7"), _
        PlotBy:=xlRows
        ' for the Range, see the comments above...
        ' here we are setting the type of lines/bars for the chart
   
    ActiveChart.SeriesCollection(1).XValues = "=ImportData!R1C2:R1C5"
    ActiveChart.SeriesCollection(1).Name = "=""Amount"""
    ActiveChart.SeriesCollection(1).AxisGroup = 2
    ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
   
    ActiveChart.SeriesCollection(2).XValues = "=ImportData!R1C2:R1C5"
    ActiveChart.SeriesCollection(2).Name = "=""Purchasing Value (MPDU)"""
    ActiveChart.SeriesCollection(2).AxisGroup = 1
    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
   
    ActiveChart.SeriesCollection(3).Name = "=""Sales Value 1 (Netto)"""
    ActiveChart.SeriesCollection(3).AxisGroup = 1
    ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
   
    ActiveChart.SeriesCollection(4).Name = "=""Sales value 2 (Brutto)"""
    ActiveChart.SeriesCollection(4).AxisGroup = 1
    ActiveChart.SeriesCollection(4).ChartType = xlLineMarkers
               
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.Name = Format("SupplierNr") & ("_") & ("Period") & ("_") & (Date)
    ' as you can see, the newly generated Sheet is not named correctly yet...
    ' the name should be 'SupplierNr'_'Period'_'DD-MM-YYYY'
    ' where SupplierNr is the SupplierNr specified in the form by the user
    ' where Period is the number of periods specified in the form by the user
        ' these two don't work yet
    ' where DD-MM-YYYY is the current date

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Supplier Name"
        ' in this example I manually entered the Title
        ' in the real macro, the Title should be automatically generated
        ' the Supplier Name can be found directly under the SupplierNr in the same column.
       
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Wert (EUR)"
        ' SeriesCollection (2),(3) and (4) depend on xlValue, xlPrimary
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Amount"
        ' SeriesCollection (1) depends on xlValue, xlSecondary
        .Axes(xlValue).HasMajorGridlines = True
        .ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
        ' here, I labeled the axis of the chart
    End With
           
    With ActiveChart.SeriesCollection(1).DataLabels.Select
    ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
    Selection.Top = 320
    ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
    Selection.Top = 320
    ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
    Selection.Top = 320
    ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
    Selection.Top = 320
    End With
    ' here, I move the DataLabels of the Amounts to halfway the Columns
    ' which they represent
    ' however, this way of moving is not so beautiful since I believe that
    ' when the size of the columns is different, they may no longer
    ' be on the right spot
    ' it would be better to take the existing position of the DataLabel
    ' and from there move it 30 (points, pixels??) lower (or 20% lower, depending on the height of the column)
    ' besides the moving, another thing is important
    ' here we are dealing with 4 Points, but when we generate a graph
    ' with 6 Periods, there should be 6 Points to move...
    ' the best solution would be to automatically select ALL
    ' Points in the SeriesCollection(4).DataLabels
    ' and then move them ALL automatically
   
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .ColorIndex = 9
        .Weight = xlThick
        ' .MarkerBackgroundColorIndex = 2
        ' .MarkerForegroundColorIndex = 9
    End With
   
    ActiveChart.SeriesCollection(3).Select
    With Selection.Border
        .ColorIndex = 6
        .Weight = xlThick
        ' .MarkerBackgroundColorIndex = 2
        ' .MarkerForegroundColorIndex = 6
    End With
   
    ActiveChart.SeriesCollection(4).Select
    With Selection.Border
        .ColorIndex = 7
        .Weight = xlThick
        ' .MarkerBackgroundColorIndex = 2
        ' .MarkerForegroundColorIndex = 7
    End With
    ' changing the style of the lines, for better viewability when printing
End Sub

************************************************
Second Macro, mostly the same problems:
************************************************

Sub Analyse_Mehrere()

' Analysis of several suppliers
' the goals is to compare the purchasing value of 3 different suppliers by entering the
' supplier numbers and the number of periods

    Charts.Add
    ActiveChart.ChartType = xl3DColumnStacked100
    ActiveChart.SetSourceData Source:=Sheets("ImportData").Range( _
        "B5:E5,B13:E13,B21:E21"), PlotBy:=xlRows
        ' again, the range should be automatically selected by the specified SupplierNr and
        ' #Periods from the Userform.
        ' Row 5 contains the Purchasing Value of Supplier1
        ' Row 13 contains the Purchasing Value of Supplier2
        ' Row 21 contains the Purchasing Value of Supplier3
    ActiveChart.SeriesCollection(1).XValues = "=ImportData!R1C2:R1C5"
    ActiveChart.SeriesCollection(1).Name = "=""SupplierNr1"""
    ActiveChart.SeriesCollection(2).XValues = "=ImportData!R1C2:R1C5"
    ActiveChart.SeriesCollection(2).Name = "=""SupplierNr2"""
    ActiveChart.SeriesCollection(3).XValues = "=ImportData!R1C2:R1C5"
    ActiveChart.SeriesCollection(3).Name = "=""SupplierNr5"""
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.Name = ("1") & ("_") & ("2") & ("_") & ("5") & ("_") & Format(Date)
    ' as you can see, the newly generated Sheet is not named correctly yet...
    ' the name should be 'SupplierNr1'_'SupplierNr2'_'SupplierNr3'_'DD-MM-YYYY'
    ' where SupplierNr are the SupplierNrs specified in the form by the user
    ' where DD-MM-YYYY is the current date
   
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Vergleich mehrere Lieferanten"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Datum"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "% von PDU-Total"
        .Axes(xlValue).AxisTitle.Orientation = xlUpward
    End With
           
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.HasDataTable = False
   
End Sub

So what we are trying to do is in fact lookup a (one or more) suppliernumber in the last filled column. The suppliernumbers are located in every 8th row (starting on row 3).  It is possible that in a certain month, suppliernr X doesn't exist. In that case, there must be an errormessage which says something like 'Supplier does not exist in this month'. It would be ideal when the macro then automatically looks in the previous column.

Please let me know if you need some more info...

Thanks for your help,

Wout