[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Add chart to active sheet with no name

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
suobs
Asked:
suobs
  • 2
1 Solution
 
macbone2Commented:
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
 
bruintjeCommented:
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
 
suobsAuthor Commented:
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
 
bruintjeCommented:
thanks for the grade and the points
0

Featured Post

Industry Leaders: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now