How to macro simple graphs

M

mikerr

Hi all, I wanted to thank the Excel community for your earlier efforts
by showing y'all how to macro a graph and save yourselves a number of
of steps.

To put this code into your excel sheet:
1. Start recording a macro. I recommend using control-a for the
hotkey. Stop recording,go into the macro code (tools->macro->macro),
and copy and paste the below code in place of what excel made.
2. In the code below, change "forecastv12" to the name of your current
excel file.

What this code does:
1. Select a range of cells with numbers in them, i.e. cell a1 through
cell a10,
2. Hit the hotkey (again, I recommend ctrl-a as it is easy to do
repeatedly).
3. You will see a simple line graph with a best-fit line complete with
equation.
4. This can be repeated as many times as you want.

If you want to customize this to your own taste and don't know how, try
putting your question here. Maybe someone will help!

Sub MakeNewGraph()
'
' MakeNewGraph Macro
' Macro recorded 8/4/2005 by ...
' Keyboard Shortcut: Ctrl+a
'
Dim sActiveSheet As String
sActiveSheet = Workbooks("Forecastv12.xls").ActiveSheet.Name
' Dim sActiveCol As String
' sActiveCol = ActiveCell.Name

Charts.Add
'Charts.Add.Name = "Column" + sActiveColumn
ActiveChart.Location Where:=xlLocationAsObject, Name:=sActiveSheet
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear,
Forward:=0, _
Backward:=0, DisplayEquation:=True,
DisplayRSquared:=True).Select
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
Selection.Left = 142
Selection.Top = 1


End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top