Kamis, 25 September 2014

[ExcelVBA] Excel chart duplication

 

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