title: Automatic plotting of graphs from different sheet
body: <p>I am programming an excel application that takes info from a Tables Sheet ( that it is also programmed and the length and position of each table can change) and generate a graphic for each table in other sheet, called Estimation Sheet, when a button is press.</p>
  
  <p>I managed to do this task for the first graphich (corresponding to first table) but when I try to use the same method for the second...it doesn't work. This is the method used to draw the first graphic:</p>
  
  <pre><code>    Public Sub generateGraphicsC(RowResistiveC As Integer)
  
         Dim FirstRow As Integer, FirstColumn As Integer, LastRow As Integer, LastColumn As Integer,         GraphLocation As Integer
         Dim XelementsC As Integer, Yelements As Integer
  
         Dim myChtObj As ChartObject
         Dim rngChtData As Range
         Dim rngChtXVal As Range
         Dim i As Integer
  
  
         Dim WSD As Worksheet
         Set WSD = Worksheets(2)     'Data source
  
         Dim CSD As Worksheet
         Set CSD = Worksheets(3)     'ChartOutput
  
         'Dim chrt As ChartObject
         'Dim cw As Long
         'Dim rh As Long
  
         ' get the current charts so proper overwriting can happen Dim chtObjs As ChartObjects
         Set chtObjs = CSD.ChartObjects
         WSD.AutoFilterMode = False       ' Turn off autofilter mode
         'Dim finalRow As Long            ' Find the last row with data
         'finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
  
  
         FirstRow = RowResistiveC
         FirstColumn = 5
  
         XelementsC = countXelementsC(FirstRow - 1, FirstColumn)                  'Count the x         Elements (amperes)
         Yelements = countYelements(FirstRow)                                      'Count the y Elements (Combinations)
  
         LastRow = FirstRow + Yelements - 1                                      'The last row and column I will read
         LastColumn = FirstColumn + XelementsC - 1
  
         '---------------------DRAW THE GRAPHIC----------------------------------------------'
  
         ' Delete any previous existing chart
          'Dim chtObj As ChartObject
  
         ' define the x axis values
         WSD.Activate
         Set rngChtXVal = WSD.Range(Cells(FirstRow - 1, FirstColumn), Cells(FirstRow - 1, LastColumn))
  
         ' add the chart
            Charts.Add
  
            With ActiveChart
            ' make a XY chart
               .ChartType = xlXYScatterLines
               ' remove extra series
               Do Until .SeriesCollection.Count = 0
                  .SeriesCollection(1).Delete
               Loop
  
               .Location Where:=xlLocationAsObject, Name:="Estimation Sheets"
            End With
  
            '-----------------------------------------------------------------------------
            With ActiveChart
               .HasTitle = True
               .ChartTitle.Characters.Text = "Factor C"
  
               'To Interpolate between the ungiven values
               .DisplayBlanksAs = xlInterpolated
  
  
                'TITLE STYLE
               .ChartTitle.AutoScaleFont = False
               With .ChartTitle.Font
                  .Name = "Calibri"
                  .FontStyle = "Bold"
                  .Size = 14
                  .Strikethrough = False
                          .Superscript = False
                  .Subscript = False
                  .OutlineFont = False
                  .Shadow = False
                  .Underline = xlUnderlineStyleNone
                  .ColorIndex = xlAutomatic
                  .Background = xlAutomatic
               End With
  
               'AXIS STYLE-----------------------------------------------------------------------
  
               .Axes(xlCategory).TickLabels.AutoScaleFont = False
               With .Axes(xlCategory).TickLabels.Font
                  .Name = "Arial"
                  .FontStyle = "Regular"
                  .Size = 10
                  .Strikethrough = False
                  .Superscript = False
                  .Subscript = False
                  .OutlineFont = False
                  .Shadow = False
                  .Underline = xlUnderlineStyleNone
                  .ColorIndex = xlAutomatic
                  .Background = xlAutomatic
               With Selection.Border
                  .ColorIndex = 15
                  .LineStyle = xlContinuous
              End With
  
  
               End With
               .Axes(xlValue).TickLabels.AutoScaleFont = False
               With .Axes(xlValue).TickLabels.Font
                  .Name = "Calibri"
                  .FontStyle = "Regular"
                  .Size = 8
                  .Strikethrough = False
                  .Superscript = False
                  .Subscript = False
                  .OutlineFont = False
                  .Shadow = False
                  .Underline = xlUnderlineStyleNone
                  .ColorIndex = xlAutomatic
                  .Background = xlAutomatic
               End With
  
            End With
            '-----------------------------------------------------------------------------
            ' HEIGHT; WIDTH AND POSITION
  
            GraphLocation = CSD.Cells(Rows.Count, 2).End(xlUp).Row + 3
  
            Dim RngToCover As Range
            Set RngToCover = ActiveSheet.Range(Cells(GraphLocation, 2), Cells(GraphLocation + 20, 11))
            With ActiveChart.Parent
               .Height = RngToCover.Height ' resize
               .Width = RngToCover.Width   ' resize
               .Top = RngToCover.Top       ' reposition
               .Left = RngToCover.Left     ' reposition
            End With
  
         ' for each row in the sheet
         For i = FirstRow To LastRow
            Dim chartName As String
            ' define chart data range for the row (record)
             Set rngChtData = WSD.Range(WSD.Cells(i, FirstColumn), WSD.Cells(i, LastColumn))
  
            'To get the serie name that I´m going to add to the graph
            Dim serieName As String
            Dim varItemName As Variant
            WSD.Activate
            varItemName = WSD.Range(Cells(i, 1), Cells(i, 4))
            serieName = CStr(varItemName(1, 1) + " " + varItemName(1, 2) + " " + varItemName(1, 3) + " " + varItemName(1, 4))
  
            ' add series from selected range, column by column
  
               CSD.ChartObjects.Select
  
  
              With ActiveChart
                  With .SeriesCollection.NewSeries
                  .Values = rngChtData
                  .XValues = rngChtXVal
                  .Name = serieName
              End With
              End With
  
          Next i
  
           'We let as last view the page with all the info
           CSD.Select
  
  
      End Sub
  </code></pre>
  
  <p>I am calling this Sub from other one. The next step will be calling a similar method (exactly the same but other starting point to get the data and some different format properties)for other kind of table and graphic:</p>
  
  <pre><code>    Public Sub printGraphics()
  
         Modul4.ClearGraphs
  
         Modul4.generateGraphicsC (RowResistiveC)
  
         Modul4.generateGraphicsT (RowResistiveT)
  
      End Sub
  </code></pre>
  
  <p>And so on. CountXelements and Yelements counts the number of elements from the Tables Sheet and RowResistiveC, for example, keeps the position of the table.</p>
  
  <p>GenerateGraphicsC works but generateGraphicsT (exactly the same) crush in the line:</p>
  
  <p><code>With .SeriesCollection.NewSeries</code></p>
  
  <p>Whit error 91 ( I have a german version of excel at work but it's something like variable object or bloque object not given).</p>