VBA Chart, too many series

[peterputter]

I have a sheet with 26 Named Ranges and I only want to plot those which I choose with the script below, however when it executes it plots them all. What am I doing wrong?

Also, the ChartTitle line uses the new chart tab name, not the originating data tab. How can I change this?

Thanks!

Sub Chart1()

Dim cht As Chart
Set cht = Charts.Add

With cht
    .ChartType = xlXYScatter
    .HasTitle = True
    .ChartTitle.Text = ActiveSheet.Name ' Would this be the original data sheet tab name!
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "=Data!$E$1"
    .SeriesCollection(1).XValues = "=CreateNames.xls!YearMth"
    .SeriesCollection(1).Values = "=CreateNames.xls!Units"
    .SeriesCollection.NewSeries
    .SeriesCollection(2).AxisGroup = xlSecondary
    .SeriesCollection(2).Name = "=Data!$F$1"
    .SeriesCollection(2).Values = "=CreateNames.xls!Unit_Cost"
    End With
   
End Sub

 

[NARAYANK991]

Hi Peter ,

See if this change helps with the chart title :

Sub Chart1()
Dim shtname as String
Dim cht As Chart
shtname = Activesheet.Name
Set cht = Charts.Add

With cht
    .ChartType = xlXYScatter
    .HasTitle = True
    .ChartTitle.Text = shtname ' Would this be the original data sheet tab name!
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "=Data!$E$1"
    .SeriesCollection(1).XValues = "=CreateNames.xls!YearMth"
    .SeriesCollection(1).Values = "=CreateNames.xls!Units"
    .SeriesCollection.NewSeries
    .SeriesCollection(2).AxisGroup = xlSecondary
    .SeriesCollection(2).Name = "=Data!$F$1"
    .SeriesCollection(2).Values = "=CreateNames.xls!Unit_Cost"
    End With
 
End Sub

Regarding your other problem , I am not clear ; the named ranges YearMth , Units and Unit_Cost refer to how many cells ?

The code is adding 2 series , and the number of points in each series depends on the number of points in the above ranges.

What are these 26 named ranges you mention ?

Narayan

 

[peterputter]

Narayan, thanks for the suggestion but I tried that before and it still uses the Chart tab name for the chart Title. As to the Named Ranges, they are all dynamic using OFFSET and COUNTA for each column. I wish I could provide you with the spreadsheet so you could see it yourself, but alas it is on another system. Which reminds me, this other system is running Office 2003... would this be an issue?

Thanks for you replies!

 

[NARAYANK991]

Hi Peter ,

The chart title is being set to the name of the Activesheet ; if prior to running the macro , the chart sheet is selected , then obviously the Activesheet is the chart sheet , and the chart title will reflect this ; if prior to running the macro , the data sheet is selected , then the activesheet is the data sheet ; how can the chart title not reflect this ?

Narayan

 

[peterputter]

I see what you're saying, and it's why I consulted the board. Monday, I will start a new sub to set this value and then call this sub (Chart1) to make the chart; fingers crossed!

 

[Hui]

Peter

can you post a sample file and highlight the ranges that you want plotted and if they change highlight this as well