Automate chart creation

andre5

Board Regular
Joined
Aug 11, 2005
Messages
108
Hi

I have a workbook with multiple sheets. I need to create the same type of chart on each sheet.
IE. I need a line chart with 3 lines. They are always in the same area but the sheet changes. series1 B6:b9 name1 b5, series2 D6:D9 name D5, series3 E6:E9 name3 E6, Category (X) axis lables are A6:A9 Location at row 13.

My bigest problem is that I do not have the sheet name which would change sheet to sheet.

How do I modify the following so that I could use it on any sheet? :rolleyes:


Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Smooth Lines"
ActiveChart.SetSourceData Source:=Sheets("Sheet165").Range("D18")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet165!R6C1:R9C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet165!R6C2:R9C2"
ActiveChart.SeriesCollection(1).Name = "=Sheet165!R5C2"
ActiveChart.SeriesCollection(2).Values = "=Sheet165!R6C4:R9C4"
ActiveChart.SeriesCollection(2).Name = "=Sheet165!R5C4"
ActiveChart.SeriesCollection(3).Values = "=Sheet165!R6C5:R9C5"
ActiveChart.SeriesCollection(3).Name = "=Sheet165!R5C5"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet165"
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
ActiveSheet.Shapes("Chart 1").IncrementLeft -116.25
ActiveSheet.Shapes("Chart 1").IncrementTop 111#
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Does this, untested, code work?
Code:
strName = ActiveSheet.Name

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Smooth Lines"
ActiveChart.SetSourceData Source:=ActiveSheet.Range("D18")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=" & strName & "!R6C1:R9C1"
ActiveChart.SeriesCollection(1).Values = "=" & strName & "!R6C2:R9C2"
ActiveChart.SeriesCollection(1).Name = "=" & strName & "!R5C2"
ActiveChart.SeriesCollection(2).Values = "=" & strName & "!R6C4:R9C4"
ActiveChart.SeriesCollection(2).Name = "=" & strName & "!R5C4"
ActiveChart.SeriesCollection(3).Values = "=" & strName & "!R6C5:R9C5"
ActiveChart.SeriesCollection(3).Name = "=" & strName & "!R5C5"
ActiveChart.Location Where:=xlLocationAsObject, Name:=strName
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
ActiveSheet.Shapes("Chart 1").IncrementLeft -116.25
ActiveSheet.Shapes("Chart 1").IncrementTop 111#
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Not tested but try:

Code:
Dim ShName as String
ShName = ActiveSheet.Name
Charts.Add 
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Smooth Lines" 
ActiveChart.SetSourceData Source:=ActiveSheet.Range("D18") 
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(1).XValues = "='" & ShName & "'!R6C1:R9C1" 
ActiveChart.SeriesCollection(1).Values = "='" & ShName & "'!R6C2:R9C2" 
ActiveChart.SeriesCollection(1).Name = "='" & ShName & "'!R5C2" 
ActiveChart.SeriesCollection(2).Values = "='" & ShName & "'!R6C4:R9C4" 
ActiveChart.SeriesCollection(2).Name = "='" & ShName & "'!R5C4" 
ActiveChart.SeriesCollection(3).Values = "='" & ShName & "'!R6C5:R9C5" 
ActiveChart.SeriesCollection(3).Name = "='" & ShName & "'!R5C5" 
ActiveChart.Location Where:=xlLocationAsObject, Name:=ShName 
With ActiveChart 
.HasAxis(xlCategory, xlPrimary) = True 
.HasAxis(xlValue, xlPrimary) = True 
End With 
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale 
ActiveSheet.Shapes("Chart 1").IncrementLeft -116.25 
ActiveSheet.Shapes("Chart 1").IncrementTop 111#
 

andre5

Board Regular
Joined
Aug 11, 2005
Messages
108
nope I get a Object doesn't support this property or method (Error 438)
error. It does not like the
ActiveChart.SetSourceData Source:=ActiveSheet.Range("D18")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When you run Andrew's or my code what sheet is actually active?

Is it a standard worksheet?
 

andre5

Board Regular
Joined
Aug 11, 2005
Messages
108
Yes it is 100% standard. just stats that needs to go into the chart
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The only way I can replicate your error is when the active sheet is actually a chart sheet.
 

andre5

Board Regular
Joined
Aug 11, 2005
Messages
108
This is what my sheet looks like and I run the macro from this sheet

A B C D E
1 Practice Name
2 Practice no
3
4 Month Claimed Total Paid member Paid to provider
5 Aug-05 R 1.00 R 1.00 R 1.00 R 1.00
6 Sep-05 R 8.00 R 2.00 R 8.00 R 2.00
7 Oct-05 R 5.00 R 3.00 R 5.00 R 3.00
8 Nov-05 R 1.00 R 5.00 R 2.00 R 4.00
 

andre5

Board Regular
Joined
Aug 11, 2005
Messages
108
When I end the Macro it creates a chart sheet. How could we stop the macro from doing this?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
andre5 said:
nope I get a Object doesn't support this property or method (Error 438)
error. It does not like the
ActiveChart.SetSourceData Source:=ActiveSheet.Range("D18")

That's because at that stage the Chart sheet is the ActiveSheet. Sorry I didn't think of that:

Code:
ActiveChart.SetSourceData Source:=Sheets(ShName).Range("D18")

Actually, I'm not sure you need that line at all, as the Series are added later.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,847
Members
412,420
Latest member
grace_abar
Top