Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Efficiently Generate 88 New Chartsheets and Populate them with Data

  Asked By: Gwen    Date: Dec 30    Category: MS Office    Views: 791
  

I've got a simple X*Y chart with year (1977-present) on the x-axis
and an estimate of deer population size on the y-axis (actually,
there may ultimately be 2 estimates/series). There is nothing
really all that fancy about it. It will be updated each year with
the current year's estimate. The problem is, I need 87 more just
like it - 1 for each of the 88 units that I manage.

I have been reading VBA and Excel books and I have yet to find an
example on how to "mass produce" a bunch of chart sheets. I take
that back - a simple "FOR NEXT" loop will generate the chart
sheets. I'm at a loss as to how to get the chart that I want with
the "look" and data that I need.

An idea thatI've had: Turn the macro recorder on and generate a
prototype chart. Cut and paste this code (after cleaning it up)
into a "FOR NEXT" loop and viola!

Clearly an oversimplification. Any sample code or other guidance
would be appreciated.

BTW the source data for each chart is all in a single worksheet and
looks like the folllowing:

County Year Population
Athens 1977 207
Athens 1978 305
Athens 2006 10,005
Belmont 1977 405

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Aiko Suzuki     Answered On: Dec 30

Here is some code  I created a few years ago. I hope it may help


Worksheets("Sheet1").Select
Range("A10:A16,A18,N10:N16,N18").Select
Range("N18").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"A10:A16,A18,N10:N16,N18"), PlotBy:=xlColumns


ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Column
Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Rental Agent's Closing
Percentages"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=3, _
Degree:=0.231372549019608
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 17
End With
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientHorizontal,
Variant:=3, _
Degree:=0.382345311665522
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 17
End With

 




Tagged:            

 
 
 
 

Related Post