Excel Macro to create a chart

Closed
Report
-
 Eugene -
Hello,

I have recorded the Excel macro 'ChartTestMacro2' below, that creatats a chart from the Sheet "MacroTestSheet". I have been unsucessful in modifying it to work for any currently selected sheet of similar structure/format. i.e. I have been unable to find the syntax to change the line -

ActiveChart.SetSource Data Source:=Sheets("MicroTestSheet").Range(

I have tried removing "Sheets("MicroTestSheet") and the STOP . from the line however the debug then dislikes the line

Range("C:C,E:E").Select

I would appreciate some advice - I believe that I am missing something obvious

In addition how might the Range accomodate different length of data i.e. instead of (C1:C40,E1:E40) as below it could accompdate (C1:Cxx,E1,Exx).


Sub ChartTestMacro2()

Range("C:C,E:E").Select
Range("E1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("MacroTestSheet").Range( _
"C1:C40,E1:E40"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="MacroTestChart2"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Chart Created by Test Macro"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis"
End With
End Sub

in anticipation, Thank to respondents.

2 replies

I need suggestion how to make code Vba Excel to grath function Y(X)
Eugen
8
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
10
Registration date
Monday January 19, 2009
Status
Member
Last seen
May 2, 2009

hi,
try this link to get inforamation about making charts:
https://exceluser.com
hope it helps you.