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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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
 
Upvote 0
Don't worry I figured out the cause of the error. I stupidly called the macro "Charts" which of course is a function.
 
Upvote 0
Yeah, that will spoil things. I noticed it when I read your first post, and forgot to say anything intelligent about it.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top