[SOLVED] Create Chart Macro from Report - Question

dontuch_dg · #1 06-21-2009, 11:15 PM

Hi, I am looking for a suggestion.

The attach file has tab Report with alot of data and charttest.
1. Can all the 6 charts be created by one macro?
2. Use a button to update?

I tried to test my 2 pie charts using the code from the website:

The bottom ActiveCharts has errors.
http://pubs.logicalexpressions.com/P...cle.asp?ID=390

Code:

Sub MyPie()
     
   ' Select the cell in the upper-left corner of the chart.
   Range("ae9").Select
   ' Select the current range of data. This line of code assumes that
   ' the current region of cells is contiguous - without empty rows
   ' or columns.
   Selection.CurrentRegion.Select
   ' Assign the address of the selected range of cells to a variable.
   myrange = Selection.Address
   ' Assign the name of the active sheet to a variable. This line is
   ' used in order to allow a chart to be created on a separate chart
   ' sheet.
   mysheetname = ActiveSheet.Name
   ' Add a chart to the active sheet.
   ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
   ' To create a chart on a separate chart sheet, remark out the
   ' previous line, and substitute the next line for the one above.
   ' Charts.Add
   Application.CutCopyMode = False
   ' This line can best be written by recording a macro, and
   ' modifying the code generated by the Microsoft Excel Macro
   ' recorder.
   ActiveChart.ChartWizard _
      Source:=Sheets(mysheetname).Range(myrange), _
      Gallery:=xlPie, Format:=8, PlotBy:=xlRows, _
      CategoryLabels:=1, SeriesLabels:=1, HasLegend:=0, _
      Title:="", CategoryTitle:="", _
      ValueTitle:="", ExtraTitle:=""
      ActiveChart.SeriesCollection(2).HasDataLabels = True      
     
End Sub

Andy Pope · #2 06-22-2009, 04:09 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Andy Pope · #3 06-22-2009, 04:17 AM

These changes wil at least get the code to run

Code:

Sub MyPie()
     
   ' Select the cell in the upper-left corner of the chart.
   Range("ae9").Select
   ' Select the current range of data. This line of code assumes that
   ' the current region of cells is contiguous - without empty rows
   ' or columns.
   Selection.CurrentRegion.Select
   ' Assign the address of the selected range of cells to a variable.
   myrange = Selection.Address
   ' Assign the name of the active sheet to a variable. This line is
   ' used in order to allow a chart to be created on a separate chart
   ' sheet.
   mysheetname = ActiveSheet.Name
   ' Add a chart to the active sheet.
   ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
   ' To create a chart on a separate chart sheet, remark out the
   ' previous line, and substitute the next line for the one above.
   ' Charts.Add
   Application.CutCopyMode = False
   ' This line can best be written by recording a macro, and
   ' modifying the code generated by the Microsoft Excel Macro
   ' recorder.
   ActiveChart.ChartWizard _
      Source:=Sheets(mysheetname).Range(myrange), _
      Gallery:=xlPie, Format:=8, PlotBy:=xlColumns, _
      CategoryLabels:=1, SeriesLabels:=1, HasLegend:=0, _
      Title:=""
'      ActiveChart.SeriesCollection(2).HasDataLabels = True
      
     
End Sub

dontuch_dg · #4 06-30-2009, 04:54 PM

Thanks Andy, I tried to remove the legend and include those labels into the pie? I am new to this site; How do I insert "solved" to threads?

Andy Pope · #5 07-01-2009, 04:12 AM

In the bar above the posts is a menu item How To...? with link to mark a thread solved.

Welcome to the Excel Forum

Forum Statistics