Vba create chart loop

[MikeL]

Hi, I want to loop thru each worksheet in my workbook and create a new stacked column chart from each table on that sheet.

Code using is:

'Set src = Selection
'With Charts.Add
' .ChartType = xlColumnStacked
' .SetSourceData Source:=src, PlotBy:=xlRows
' .Location Where:=xlLocationAsObject, Name:="Sheet1"


How do I change code to reference current ws and not Sheet1 as it loops?

Thanks in advance

 

[Domenic]

Try something like this...

    Dim currentWorksheet As Worksheet
    Dim currentChart As Chart
    
    For Each currentWorksheet In ActiveWorkbook.Worksheets
        Set currentChart = currentWorksheet.Shapes.AddChart2(XlChartType:=xlColumnStacked).Chart
        With currentChart
            'set chart properties
            '
            '
        End With
    Next currentWorksheet

Note that you can set the properties for Left, Top, Width, and Height using the AddChart2 method, which contains optional parameters for those properties...

[COLOR=#574123]Set currentChart = currentWorksheet.Shapes.AddChart2(XlChartType:=xlColumnStacked, Left:=currentWorksheet.Range("B2").Left, ...).Chart[/COLOR]

Hope this helps!

 

[MikeL]

thanks!