creating multiple charts with macro

**furor** · 09-20-2011, 05:28 PM

Dear all,

is it possible to write a macro so that several (about 50) graphs are made based on the same template?
Or if you could refer me to an analogous topic where I could use the code with some adjustments (I might just be able to do that

).

It's quite straightforward, but time-consuming doing it manually ...

It's about bird species (the title)
numbers of observed individuals in a certain month
the months as labels for the x-axis.
add values and remove legend

I can record a macro, but it will just perform the same thing over and over, not shifting to the next species and data ...

**tlafferty** · 09-20-2011, 06:35 PM

This needs testing:

Sub grafieken()
'
' grafieken Macro
' De macro is opgenomen op 20/09/2011 door Bjorn.
'
' Sneltoets: CTRL+g
'
Dim lngLastRow As Long, strName

'initialize range variable
    lngLastRow = Sheets("grafieken").Range("B65536").End(xlUp).Row
    
'loop through data
    For Each c In Sheets("grafieken").Range("B2:B" & lngLastRow)
    'initialize chart name variable
    strName = Sheets("grafieken").Range("B" & c.Row).Value
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("grafieken").Range("J2")
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(1).XValues = "=grafieken!R1C3:R1C8"
        ActiveChart.SeriesCollection(1).Values = "=grafieken!R" & c.Row & "C3:R" & c.Row & "C8"
        ActiveChart.SeriesCollection(1).Name = "=grafieken!R" & c.Row & "C2"
        ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=strName
        ActiveChart.HasLegend = False
    Next
End Sub

**furor** · 09-21-2011, 03:41 PM

Hi Thomas,

this almost does the job perfectly!
However, I get an error message on line

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=strName

.

Also, is it possible to create them in the active worksheet? When I copy the graph and adjust the size to fit in Word, the axis labels diminish in size and become unreadable.

I also added this line but is doesn't work (adding values through re-recording the macro):
ActiveChart.HasDataTable = False

Many thanks!

**tlafferty** · 09-21-2011, 03:57 PM

Sure. The amended code puts them all in the sheet with the data. Unfortunately, they are one on top of the other, so as you copy each one to word, you could delete the chart from Excel and have a handy means of tracking your progress. Also, the reason the code was bombing was there was a gap at the bottom of your data where you put in aantal soorten (total species?). In any case, removing the blank row takes care of the problem.

Sub grafieken()
'
' grafieken Macro
' De macro is opgenomen op 20/09/2011 door Bjorn.
'
' Sneltoets: CTRL+g
'
Dim lngLastRow As Long, strName

'initialize range variable
    lngLastRow = Sheets("grafieken").Range("B65536").End(xlUp).Row
    
'loop through data
    For Each c In Sheets("grafieken").Range("B2:B" & lngLastRow)
    'initialize chart name variable
    strName = Sheets("grafieken").Range("B" & c.Row).Value
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("grafieken").Range("J2")
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(1).XValues = "=grafieken!R1C3:R1C8"
        ActiveChart.SeriesCollection(1).Values = "=grafieken!R" & c.Row & "C3:R" & c.Row & "C8"
        ActiveChart.SeriesCollection(1).Name = "=grafieken!R" & c.Row & "C2"
        ActiveChart.Location Where:=xlLocationAsObject, Name:="grafieken"
        ActiveChart.HasLegend = False
    Next
End Sub

See attached book.

**furor** · 09-21-2011, 05:19 PM

Thanks man, this works great!

I owe you

aantal soorten = number of species (in Dutch)

**shep2010** · 10-22-2011, 09:33 AM

Hi,

I'm not sure if you can help, i'd appreciate it it a lot if you could. I need a chart for multiple merchants to track suspicious sales. e.g i would chart certian sales for a month (30 or 31 days) and look for spikes.

i need to produce around 50 charts a day to identify these spikes and evidence tht we have monitored a certian merchants.

The format is the first column has merchant names and then each row contains represents each days figures going through the merchant. in the charts i'd like to have the date represented in the axis e.g 01/10/2011 etc. As I need to look at and document each of these and to produce multiple tables is very time consuming. i'd appreciate any help with this.

attached is an example of the data/format. Any help would be appreciated.

All the best,

Steve