Creating the same chart different workbook different sheet

MrMT

New Member
Joined
Jul 13, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
Here's how you might use the activesheet name in your macro:

Code:
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

Code:
  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

New Member
Joined
Jul 13, 2011
Messages
3
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

New Member
Joined
Jul 13, 2011
Messages
3
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
Yeah, that will spoil things. I noticed it when I read your first post, and forgot to say anything intelligent about it.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,363
Messages
5,444,023
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top