Creating the same chart different workbook different sheet

[MrMT]

Hi,
I have lots of workbooks detailing specific data, I have started creating overview/summary charts detailing specific columns in each workbook. The format of the chart is the same, the columns the data is taken from are the same in each workbook.
The two variables are the Workbook Name and Sheet Name. I've tried recording a macro, in the hope of running it from a master-workbook to create a chart on all the individual workbooks as I go through them. But, it doesn't work.
I'm guessing there is a way of setting "sheet1" to be the current Active sheet, but I don't know how to do this.
Any help would be greatly appreciated, it would be nice to cut down a potential 2 weeks worth of chart making to about an afternoon with a nice simple macro.



Sub Charts()
'
' Charts Macro
'
'
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Best overview"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("G18")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!R6C2:R135C2"
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!R6C5:R135C5"
ActiveChart.SeriesCollection(1).Name = "=""series1"""
ActiveChart.SeriesCollection(2).XValues = "='Sheet1'!R6C2:R135C2"
ActiveChart.SeriesCollection(2).Values = "='Sheet1'!R6C19:R135C19"
ActiveChart.SeriesCollection(2).Name = "=""series2"""
ActiveChart.SeriesCollection(3).XValues = "='Sheet1'!R6C2:R135C2"
ActiveChart.SeriesCollection(3).Values = "='Sheet1'!R6C18:R135C18"
ActiveChart.SeriesCollection(3).Name = "=""series3"""
ActiveChart.SeriesCollection(4).XValues = "='Sheet1 '!R6C2:R135C2"
ActiveChart.SeriesCollection(4).Values = "='Sheet1'!R6C7:R135C7"
ActiveChart.SeriesCollection(4).Name = "=""series4"""
ActiveChart.SeriesCollection(5).XValues = "='Sheet1'!R6C2:R135C2"
ActiveChart.SeriesCollection(5).Values = "='Sheet1'!R6C8:R135C8"
ActiveChart.SeriesCollection(5).Name = "=""series5"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Overview"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "ug/l"
End With
End Sub

 

[Jon Peltier]

Here's how you might use the activesheet name in your macro:

Sub Charts()
'
' Charts Macro
'

  Dim shtActive As Worksheet
  Dim sShtName As String

  Set shtActive = ActiveSheet
  sShtName = "'" & shtActive.Name & "'"

  Charts.Add
  ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
    "Best overview"
  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection(1).XValues = "=" & strShtName & "!R6C2:R135C2"
  ActiveChart.SeriesCollection(1).Values = "=" & strShtName & "!R6C5:R135C5"
  ActiveChart.SeriesCollection(1).Name = "=""series1"""
  ActiveChart.SeriesCollection(2).XValues = "=" & strShtName & "!R6C2:R135C2"
  ActiveChart.SeriesCollection(2).Values = "=" & strShtName & "!R6C19:R135C19"
  ActiveChart.SeriesCollection(2).Name = "=""series2"""
  ActiveChart.SeriesCollection(3).XValues = "=" & strShtName & "!R6C2:R135C2"
  ActiveChart.SeriesCollection(3).Values = "=" & strShtName & "!R6C18:R135C18"
  ActiveChart.SeriesCollection(3).Name = "=""series3"""
  ActiveChart.SeriesCollection(4).XValues = "=" & strShtName & "!R6C2:R135C2"
  ActiveChart.SeriesCollection(4).Values = "=" & strShtName & "!R6C7:R135C7"
  ActiveChart.SeriesCollection(4).Name = "=""series4"""
  ActiveChart.SeriesCollection(5).XValues = "=" & strShtName & "!R6C2:R135C2"
  ActiveChart.SeriesCollection(5).Values = "=" & strShtName & "!R6C8:R135C8"
  ActiveChart.SeriesCollection(5).Name = "=""series5"""
  ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Overview"
  With ActiveChart
    .HasTitle = False
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "ug/l"
  End With
End Sub

I didn't change where you're placing the chart, but if it goes onto this sheet, you can use

  ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=shtActive.Name

Use "code tags" when posting code. At the beginning put

{opensquarebracket}code{closesquarebracket}

and at the end put

{opensquarebracket}/code{closesquarebracket}

and your code will be formatted nicely, including any indentation.

 

[MrMT]

Many thanks for getting back to me.
The code looks good, but I am getting the following message

" Compile Error: Expected Function or variable"

whilst highlighting Charts.Add when I try running the macro

Any ideas?

Cheers

Martyn

 

[MrMT]

Don't worry I figured out the cause of the error. I stupidly called the macro "Charts" which of course is a function.

 

[Jon Peltier]

Yeah, that will spoil things. I noticed it when I read your first post, and forgot to say anything intelligent about it.