Export charts from active sheet [VBA]

[Martin_H]

Hi team,

I am using this code to export charts from all sheets in active workbook.

I would like to convert the code to export charts only from active sheet.

Thank you.

Sub ExportCharts()
    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String

    Dim objChrt As ChartObject
    Dim myChart As Chart
    
    user = Environ("Username")
    SaveToDirectory = "C:\Users\" & user & "\Desktop\"

    For Each WS In ActiveWorkbook.Worksheets
        WS.activate
        For Each objChrt In WS.ChartObjects
            objChrt.activate
            Set myChart = objChrt.Chart

            myFileName = SaveToDirectory & WS.Name & "_" & objChrt.Index & ".png"

            On Error Resume Next
            Kill SaveToDirectory & WS.Name & Index & ".png"
            On Error GoTo 0

            myChart.Export Filename:=myFileName, Filtername:="PNG"
        Next
    Next

    MsgBox "OK"
End Sub

 

[smozgur]

You have missing variable declarations (user, myFileName) and I believe the Index variable used in the Kill statement should be objChrt.Index. However, for the original request, following changes should work.

Sub ExportCharts()
    Dim WS As Excel.Worksheet
    Dim SaveToDirectory As String

    Dim objChrt As ChartObject
    Dim myChart As Chart
    
    user = Environ("Username")
    SaveToDirectory = "C:\Users\" & user & "\Desktop\"

    'For Each WS In ActiveWorkbook.Worksheets
        'WS.activate
        Set WS = ActiveSheet
        For Each objChrt In WS.ChartObjects
            objChrt.activate
            Set myChart = objChrt.Chart

            myFileName = SaveToDirectory & WS.Name & "_" & objChrt.Index & ".png"

            On Error Resume Next
            Kill SaveToDirectory & WS.Name & Index & ".png"
            On Error GoTo 0

            myChart.Export Filename:=myFileName, Filtername:="PNG"
        Next
    'Next

    MsgBox "OK"
End Sub