How to reference the active sheet (rather than defining a sheet name) in chart series names & x/y lists

[rover8721]

Hi all,

I'm writing a VBA macro in Excel (2007) to generate a series of plots. This macro is intended to get iterated across numerous sheets and workbooks. Because of this, I need the macro be generalized and the only stumbling block I'm encountering right now is that Excel is forcing me to specify a sheet name when listing series names & x/y values. I have tried a number of different approaches but am still unable to find a way to have Excel use the active sheet so the macro can be applied to any sheet/workbook. I've seen some ways to define an activesheet as a variable and apply that, but nothing still seems to work within a chart series.

So, in summary, how might I be able to reference the active sheet in a chart series so I can apply the generalized macro to any sheet?

Thanks for reading this!

 

[RickXL]

Hi,

Could you give us a sample of your code so far so that we can see how you are trying to set up the ranges?

Thanks,

 

[rover8721]

Thanks for the reply!

The following code is a representative sample of what works (with the specific sheet [named "temp"] reference):

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Drop Rate"""
    ActiveChart.SeriesCollection(1).Values = "='temp'!$CN$9,'temp'!$CZ$9,'temp'!$DL$9"
    ActiveChart.SeriesCollection(1).XValues = "='temp'!$AY$1:$BA$1"

I would like to replace the specific sheet name with a universal tag for the active sheet. Any removal or adjustment of that portion <'temp'!> causes an error at line 5.

 

[RickXL]

I think this works:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Drop Rate"""
    ActiveChart.SeriesCollection(1).Values = Union(Range("$CN$9"), Range("$CZ$9"), Range("$DL$9"))
    ActiveChart.SeriesCollection(1).XValues = Range("$AY$1:$BA$1")

 

[RickXL]

This is shorter. I could not remember the syntax before. I am getting too old for this.

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Drop Rate"""
    ActiveChart.SeriesCollection(1).Values = Range("$CN$9, $CZ$9, $DL$9")
    ActiveChart.SeriesCollection(1).XValues = Range("$AY$1:$BA$1")

 

[rover8721]

This is shorter. I could not remember the syntax before. I am getting too old for this.

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Drop Rate"""
    ActiveChart.SeriesCollection(1).Values = Range("$CN$9, $CZ$9, $DL$9")
    ActiveChart.SeriesCollection(1).XValues = Range("$AY$1:$BA$1")

Fantastic! That works absolutely perfectly. Thank you so, so very much for resolving this! I thought it must be fairly simple, but countless searches came up with nada. Hopefully, this will help others afflicted from the same problem. Thank you!

 

[RickXL]

No problem, I am pleased we got it working.

Searching is OK but often all it reveals is how many different ways Excel can do something when what you really want to know is how to change your existing code. That is where using MrExcel scores. Also, posting your "work so far" is a useful starting point.

Regards,