Macro for charting across sheets

  • Thread starter Thread starter Jan Kåre
  • Start date Start date
J

Jan Kåre

Hi!

Excel 2000: Have some 50 identical tables on different sheets
(Table_1 ..Table_50). Trying to make a macro that creates same type of pie
chart for each. Unable to make one work across sheets.

Anyone know a way ? (Pasting in my recorded one below)

Search and replace the name "Table_1" before each run works, but...

New to macros. Please help.



//

Sub Make_pie_cht()



Range("A5:D5,A2:D2").Select

Range("A2").Activate

Charts.Add

ActiveChart.ChartType = xlPie

ActiveChart.SetSourceData
Source:=Sheets("Table_1").Range("A2:D2,A5:D5"), _

PlotBy:=xlRows

ActiveChart.Location Where:=xlLocationAsObject, Name:="Table_1"

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent,
LegendKey _

:=False, HasLeaderLines:=True

End Sub

//
 
Jan Kåre -

You need to generalize the range and the worksheet. I have a procedure below which
puts the selected range into a range variable and the active sheet into a worksheet
variable. (If no range is selected, it bails out.) The chart is created, the range
variable is used as the source data, the chart is moved onto the worksheet.

Sub Make_pie_cht()
Dim wsActive As Worksheet
Dim rSelection As Range
Set wsActive = ActiveSheet
If TypeName(Selection) <> "Range" Then Exit Sub
Set rSelection = Selection
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=rSelection, _
PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:=wsActive.Name
ActiveChart.PlotArea.Border.LineStyle = xlNone
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent, _
LegendKey:=False, HasLeaderLines:=True
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks a lot, Jon! You just saved me from hours of monotonous work!

One more: Is there a starting point for someone wanting to learn elementary
macro / VBA language that you would recommend? Millions on the net. Where to
start?
 
Well, if you're willing to part with a few bucks, John Walkenbach has just published
VBA for Dummies. I haven't seen it, but if it's like his other books, it's going to
be very helpful.

I have some information about VBA and charts on my web site:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

I learned about VBA mostly on the net. I think I used Google to look up a keyword
that I needed help with. A few good sites dealing with VBA:

Chip Pearson - http://cpearson.com
John Walkenbach - http://j-walk.com
David McRitchie - http://mvps.org/dmcritchie/excel/excel.htm#tutorials

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top