We help IT Professionals succeed at work.

Creating multiple indivdual charts vs. one chart with multiple series

WhackyAlaskan
on
289 Views
Last Modified: 2012-05-11
Very new to VBA so please be gentle.  I have some some dynamic data varying in row and column length.  The labels are in column A with the data spaning several columns.  I'm trying to create a singe run chart for each column of data.  My first crack at the code gives me the charts, but the 1st has one series of data, the 2nd has two series, etc.  

How do I change the code to limit the chart to just the data on that column?  

Thanks
Sub My_chart()
    Dim MyRng As Range
    Dim MyDte As Range
    Dim arow As Integer
    Dim acol As Integer
    Range("A1").Activate
    
    arow = Range("A1").SpecialCells(xlLastCell).Row
    acol = Range("A1").SpecialCells(xlLastCell).Column
    
    Set MyDte = Range("B1:B" & arow)
    Set MyRng = Range("$A$1:$A$" & arow, MyDte)
    
    Do
     MyRng.Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=MyRng
        ActiveChart.ChartType = xlLine
        MyDte.Offset(0, 1).Select
        Set MyDte = MyDte.Offset(0, 1)
        Set MyRng = Range("$A$1:$A$" & arow, MyDte)
    Loop While Not IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Open in new window

Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
That did it. I found that it was missing the last column of data so set
Loop While Not IsEmpty(ActiveCell.Offset(0, 0)) and it works perfectly.  

Thanks!

Author

Commented:
Thanks for the rapid response!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.