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?

Who is Participating?
bruintjeConnect With a Mentor Commented:
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")
  ActiveChart.ChartType = xlColumnClustered
  ActiveChart.SetSourceData Source:=Sheets(strSheetName).Range(rngChartRange), _
  ActiveChart.Location Where:=xlLocationAsObject, _
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

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
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
    ActiveChart.ChartType = xlLineStacked
    ActiveChart.SetSourceData Source:=Sheets(sheetname1).Range(SSRange), PlotBy:= _
    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
(mins) Money Lost
20     10
40     30
60     50
80     60
100     50
120     90
140     120
160     200
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!
thanks for the grade and the points
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.