Hi all. Excel 2010, I have to create multiple charts on a data set that spans about a year of history, one month per sheet, AND each sheet has ~60 lines of data. For ease of readability, the first chart has only six data series that contain a fixed x-axis, data as y-axis and a series name that uses CONCATENATE for a descriptive Legend.
No problem in creating the first chart, and easy to copy and paste the chart, and then by clicking on each series in the chart, moving the ranges for x-axis, y-axis, and name fields.
How do I automate this?
1) Recorded a macro during creation of first chart and modified to that below. I need help with my concatenate formula in the second part of code to state that the selcted data will be the x-axis in row 8, and then a variable data range in groups of six rows at a time.
Regards
Paul
Sub GenTenCharts()
'This code generates ten size distribution charts on the active sheet.
'It is assumed that the data still lives in columns T to AA.
'Column S is populated with sample number and contractor name.
Dim i As Long
'Concatenate values to column S for ease of charting.
For i = 9 To 75
Range("S" & i).Value = Range("E" & i).Value & " " & Range("C" & i).Value
Next i
'Generate the charts
For i = 9 To 69 Step 6
Range("S8:AA8,"S"&i&":AA"&i+5).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=ActiveSheet.Range("$S$8:$AA$14")
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScale = 0.01
ActiveChart.Axes(xlCategory).MaximumScale = 10
ActiveChart.Axes(xlCategory).ScaleType = xlLogarithmic
ActiveChart.Axes(xlCategory).CrossesAt = 0.01
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MaximumScale = 1.2
ActiveChart.Axes(xlValue).MaximumScale = 1
Selection.TickLabels.NumberFormat = "0%"
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).HasMajorGridlines = True
ActiveChart.Axes(xlCategory).HasMinorGridlines = True
Next i
End Sub
Posted by: paul.vermeulen@vulcantech.com.au
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar