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#
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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#
 
Upvote 0
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#
 
Upvote 0
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")
 
Upvote 0
When you run Andrew's or my code what sheet is actually active?

Is it a standard worksheet?
 
Upvote 0
The only way I can replicate your error is when the active sheet is actually a chart sheet.
 
Upvote 0
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
 
Upvote 0
When I end the Macro it creates a chart sheet. How could we stop the macro from doing this?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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