Looping through sheets and creating charts issue

Hello folks,

I have following code which is not behaving the way I want.

Dim current As Worksheet
Dim chart_name As String
Dim curr As String
Dim Columns As ListColumns
Dim temp As String
Dim listobj As ListObject
Dim new_sheet As Worksheet

    For Each current In ThisWorkbook.Sheets        
         curr = listobj.Name
        temp = curr & "[[#All],[Column1]]," & curr & "[[#All],[Column2]]"
        current.Shapes.AddChart.Select
        ActiveChart.ChartType = xlColumnStacked
        chart_name = "chart_" & current.Name
        Debug.Print temp
        ActiveChart.SetSourceData Source:=Range(temp)
        ActiveChart.ApplyLayout (1)
        ActiveChart.ChartTitle.Select
        ActiveChart.ChartTitle.Text = "Y: Stacked PKI of instr pairs"
        Selection.Format.TextFrame2.TextRange.Characters.Text = _
            "Y: Stacked PKI of instr pairs"
        With Selection.Format.TextFrame2.TextRange.Characters(1, 29).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
        ActiveChart.ChartArea.Select
        ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=chart_name
    Next

The problem is the last statement, a chart which gets created in an iteration does not "move" to a new sheet but rather same sheet. So end result is a sheet with chart of the last iteration.

How to get this code to create new charts in new sheet and not the same sheet. A help will be greatly appreciated.

Not really clear from your description what you want.

Why not try recording the code and then replacing the last biit with correct syntax.

You probably want to loop through Worksheets rather than Sheets, as sheets includes chartsheets which will not have ranges on it.

I just tried something and it seems to work fine for me (1 new sheet for each chart) but only after I change ThisWorkbook to ActiveWorkbook.

This was what I did:

Dim current As Worksheet
Dim chart_name As String
Dim curr As String
Dim Columns As ListColumns
Dim temp As String
Dim listobj As ListObject
Dim new_sheet As Worksheet

    For Each current In ActiveWorkbook.Sheets
        'curr = listobj.Name
        'temp = curr & "[[#All],[Column1]]," & curr & "[[#All],[Column2]]"
        With current
            chart_name = "chart_" & .Name
            With .Shapes.AddChart(xlColumnStacked)
                .Chart.SetSourceData Source:=current.Range("A1").CurrentRegion 'Range(temp)
                .Chart.ApplyLayout 1
                .Chart.ChartTitle.text = "Y: Stacked PKI of instr pairs"
                .Chart.ChartTitle.Format.TextFrame2.TextRange.Characters.text = _
                    "Y: Stacked PKI of instr pairs"
                With .Chart.ChartTitle.Format.TextFrame2.TextRange.Characters(1, 29).ParagraphFormat
                    .TextDirection = msoTextDirectionLeftToRight
                    .Alignment = msoAlignCenter
                End With
                .Chart.Location Where:=xlLocationAsNewSheet, Name:=chart_name
            End With
        End With
    Next