Excel macro for creating and manipulating charts

Hi Friends, I have written this macro which tells how we can creats and manipulates the charts. I have also attached the Excel sheet that I have used in given example. You can copy paste this macro and it will start working. ( You will need to press Alt-F11 to go to VBA editor and copy paste this code and make sure that your sheet name is Sheet1. Press F5 to run this macro). Your questions are most wecomed. Enjoy!!! -Jack

The macro itself says eveything what the portion of code does. Please find the Excel sheet I have used in this example at http://www.eggheadcafe.com/fileupload/1676974893_Book1.zip

The complete macro is below and the resulted charts of code portions are given at the end.

Sub myCharts()     'will generate mychart1
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
   
    'will generate mychart2
    With ActiveSheet.ChartObjects.Add _
            (Left:=200, Width:=250, Top:=100, Height:=200)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
        .Chart.ChartType = xlXYScatterLines
    End With
   
    ' Remove series will generate mychart3 which is completely blank
    ' Remove this code if you want to display all the series
    With ActiveChart
        'Do Until .SeriesCollection.Count = 0
            '.SeriesCollection(1).Delete
        'Loop
    End With

    ' Reposition the charts statically
    With ActiveChart.Parent
        .Left = 100
        .Width = 375
        .Top = 75
        .Height = 225
    End With

    ' Align all the charts so that all are visible at different positions
    ' mychart4
    Dim i As Long
    Dim totalCharts As Long
    Dim cTopMargin As Double
    Dim cLeftMargin As Double
    Dim cHeightMargin As Double
    Dim cWidthMargin As Double
    Dim cColumns As Long     cTopMargin = 75
    cLeftMargin = 100
    cHeightMargin = 225
    cWidthMargin = 375
    cColumns = 3
    totalCharts = ActiveSheet.ChartObjects.Count     For i = 1 To totalCharts
        With ActiveSheet.ChartObjects(i)
            .Height = cHeightMargin
            .Width = cWidthMargin
            .Top = cTopMargin + Int((i - 1) / cColumns) * cHeightMargin
            .Left = cLeftMargin + ((i - 1) Mod cColumns) * cWidthMargin
        End With
    Next End Sub


The following macro function will display the all the values attached with the graph in the worksheet:

Sub PrintChartValues()
  
        Dim nrows As Integer
        Dim X As Object
        Counter = 2
       
        nrows = UBound(ActiveChart.SeriesCollection(1).Values)         Worksheets("ChartData").Cells(1, 1) = "X Values"         With Worksheets("ChartData")
            .Range(.Cells(2, 1), _
            .Cells(nrows + 1, 1)) = _
            Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
        End With         For Each X In ActiveChart.SeriesCollection
            Worksheets("ChartData").Cells(1, Counter) = X.Name             With Worksheets("ChartData")
                .Range(.Cells(2, Counter), _
                .Cells(nrows + 1, Counter)) = _
                Application.Transpose(X.Values)
            End With             Counter = Counter + 1
        Next End Sub


Resulted Charts:
---------------

mychart1:

mychart2:

mychart3:



mychart4: