asked on

Looping thru named ranges and create charts

Dear Experts:

below code ...
... loops thru all the list objects of the active sheet (data lists formatted as tables)
... and formats the chart

I would like the code to loop through all named ranges instead of list objects (defined tables) and create the charts from them. I got no idea how to rewrite the code.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

For Each t In ActiveSheet.ListObjects
    Set MyChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=400, Top:=75, Height:=200)
    
   With MyChtObj.Chart
       .SetSourceData Source:=Sheets("Sheet2").Range(t)
            .ChartType = xlBarClustered
            .HasLegend = False
             With .Axes(xlCategory)
                 .MajorTickMark = xlNone
                 .TickLabels.Font.Name = "Arial"
                 .TickLabels.Font.Size = 9.5
             End With
            .SeriesCollection(1).ApplyDataLabels
             With .SeriesCollection(1).DataLabels
                  .Position = xlLabelPositionInsideEnd
                  .Font.Color = RGB(255, 255, 255)
                  .Font.Name = "Arial"
                  .Font.Size = 9.5
                End With
             With .Axes(xlValue)
                  .MinimumScale = 0
                  .MaximumScale = 10
                  .Delete
                  .MajorGridlines.Delete
             End With
    
        End With
  
        With MyChtObj.Chart
            .SetElement (msoElementChartTitleAboveChart)
            With .ChartTitle
                 .Text = "=Sheet2!$B$6"
                 .Font.Name = "Arial"
                 .Font.Size = 12
            End With
        End With
        

        With MyChtObj.Chart.Parent 'Change location so they're not on top of each other
            .Top = Range("A7").Top + i * (MyChtObj.Height + 80)
            .Left = Range("E7").Left
            .Name = "Chart" & i + 1
        End With
        i = i + 1
Next

End Sub

Open in new window

ragnarok89

I think what you want it:

Sub Test()
Dim N As Name
For Each N In Sheet1.Names
"do stuff"
Next N
End Sub

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Dear rorya:

thank you very much for your quick help. I am getting erroneous results but it may be because I am running the code on Excel 2010. This version has a couple of charting bugs.

I will run the code on my machine at my working place (Excel 2007) and then let you know what happened. Again, thank you very much for your professional support.

Regards, Andreas

Andreas Hermle

ASKER

rorya:
Great job rorya!!
ok, now it works just fine. But it has nothing to with Excel 2010 and Excel 2007, respectively, as I initially thought.
Thank you very much for your great and professional help. What would I do without this forum?

Regards, Andreas