Make charts from each worksheet in a workbook 1

[bodo62]

Hi!

I have a Workbook containing several worksheets and would like to loop through all sheets and make a graph of them.

The workbook is updated every period of time and the contents may vary for each period (different names for the worksheets and different amount of sheets every new period)

I have recorded a macro to build one chart, but I would need to make a loop through all worksheets and build one chart for each sheet.

Where there is "SA_AR_PD" in the code I'd like to get each worksheet name instead:

Sub SAgraph()

    Range("D1:I14").Select
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("SA_AR_PD").Range("D1:I14"), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="SA_AR_PD_GR"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "SERVICE LEVEL " & Sheets("SA_AR_PD").Range("A2")
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "%"
    End With
    ActiveChart.HasLegend = False
    ActiveChart.HasDataTable = True
    ActiveChart.DataTable.ShowLegendKey = True
End Sub

 

[Loomah]

Hi
This should get you started

Sub a()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    'Range("D1:I14").Select
    Charts.Add
    With ActiveChart
        .ChartType = xlLineMarkers
        .SetSourceData Source:=ws.Range("D1:I14"), PlotBy _
            :=xlColumns
            
    'WILL NEED UNIQUE NAME FOR EACH SHEET - WHAT IS THE IDENTIFIER?
    'COULD USE :
    'Name:=ws.Name & "_GR"  ???????
        .Location Where:=xlLocationAsNewSheet, Name:="SA_AR_PD_GR"
    
        .HasTitle = True
        .ChartTitle.Characters.Text = "SERVICE LEVEL " & ws.Range("A2")
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "%"
    
        .HasLegend = False
        .HasDataTable = True
        .DataTable.ShowLegendKey = True
    End With
Next
End Sub

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bodo62]

Thanks Loomah, that was what I was looking for ...



If I want to get some specific worksheets instead of all, let's say all sheets with sheet names starting with "SA" or "DP" how could I do that?

 

[Loomah]

Just test for that instance or instances as follows

For Each ws In ThisWorkbook.Worksheets
[b]If Left(ws.Name, 2) = "sa" Or Left(ws.Name, 2) = "dp" Then[/b]
    Charts.Add after:=ws
    With ActiveChart
          'the chart code here
    End With
[b]End If[/b]
Next

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bodo62]

Thanks again Loomah, I really appreciate your help.

I now have one last little problem:

Some of the worksheets contain less rows than others and since I have selected a range (D1:I14) the charts get strange where the rows are less than 14. I've looked into the FAQ's about how to find last row, but I got stuck while trying to use the code and getting error messages.

The worksheets contain a fixed number of columns, but the number of rows is variable from 2 to 14.

Thanks in advance.

Have a nice weekend (-:

 

[Loomah]

Hi
There are a number of possible solutions to this one depending on how your data looks.

If the source stands alone ie nothing to the immediate left or right and nothing immediately below and always starts in D1 then

.SetSourceData Source:=ws.Range("D1").CurrentRegion, PlotBy _
    :=xlColumns

should do the trick

As long as there are at least 2 row then this should also work. Again assuming data will always start in D1

.SetSourceData Source:=ws.Range("D1:I" & _
    ws.Range("D1").End(xlDown).Row), PlotBy:=xlColumns

If there is nothing under the data then this may be a safer option

.SetSourceData Source:=ws.Range("D1:I" & _
    ws.Range("D65536").End(xlUp).Row), PlotBy:=xlColumns

Other methods involve assigning the value of the last row to a variable using methods highlighted in the FAQs and adapting them. That's probably a bit overkill for this situation though!

You could always assign the value of he last row in the 2nd & 3rd methods I've given here and that would make your code easier to read
eg

lRow = ws.Range("D65536").End(xlUp).Row
.SetSourceData Source:=ws.Range("D1:I" & lRow), PlotBy:=xlColumns

where lRow is Dim'd as Long

I'll certainly try to have a good weekend. Hope you do too!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bodo62]

Great!

Once again thanks Loomah for your help.