Solved

Add chart to active sheet with no name

Posted on 2002-04-05
4
162 Views
Last Modified: 2012-08-13
I want to write a macro to add charts to  a number of worksheets.  The code produced when I record a macro graphs the data from  the worksheet where I recorded it (e.g., "Sheet1").   How can I do this so the chart is placed on the ACTIVE worksheet?

0
Comment
Question by:suobs
  • 2
4 Comments
 
LVL 2

Expert Comment

by:macbone2
ID: 6922203
The code below will place a chart in each worksheet in the book. I'm presuming your data is in columns a & b in each sheet. Try placing the data below in cols A & B, then copy and paste the code below into a VBA macro, then run it or step thru it.

Sub charter()
Dim sheetname1 As String, shtscnt As Long, rowscount As Long
Dim SelRange As String, SSRange As String
For shtscnt = 1 To Sheets.Count

sheetname1 = Sheets(shtscnt).Name
Worksheets(sheetname1).Activate
rowscount = Range("A1").CurrentRegion.Rows.Count
SelRange = "A1:A" & rowscount & ", B1:B" & rowscount
SSRange = "A1:B" & rowscount
 '''replaced by string value   Range("A1:A9,B1:B9").Select
    Range(SelRange).Select
    Charts.Add
    ActiveChart.ChartType = xlLineStacked
    ActiveChart.SetSourceData Source:=Sheets(sheetname1).Range(SSRange), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sheetname1
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Roulette (phewey)"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    Next shtscnt
End Sub

The range in column a would be Times and col B values
eg
Time
(mins) Money Lost
20     10
40     30
60     50
80     60
100     50
120     90
140     120
160     200
0
 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
ID: 6922365
Hi Suobs,

-the following code will
-use the active sheet
-as a source
-as a place

Public Sub ChartCustomRange()
Dim strSheetName As String
Dim rngChartRange As String
  strSheetName = ActiveSheet.Name
  rngChartRange = InputBox("Give range to chart", "Chartrange", "A10:D25")
  Charts.Add
  ActiveChart.ChartType = xlColumnClustered
  ActiveChart.SetSourceData Source:=Sheets(strSheetName).Range(rngChartRange), _
    PlotBy:=xlColumns
  ActiveChart.Location Where:=xlLocationAsObject, _
    Name:=strSheetName
End Sub

-for different chart types you can play around with this line
  ActiveChart.ChartType = xlColumnClustered
-by using intellisense on the part
  ActiveChart.ChartType = xl[click CTRL+TAB here]

-a good resource on charting and coding in excel
http://www.geocities.com/jonpeltier/Excel/Charts/

HTH:O)Bruintje
0
 

Author Comment

by:suobs
ID: 6923675
Macbone2's macro is very cool, but works on multiple pages. Bruintje's works on individual pages, exactly what I was looking for, so gets the points.
 
Thanks both of you!
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6923681
thanks for the grade and the points
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now