Solved

Add chart to active sheet with no name

Posted on 2002-04-05
4
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

726 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