CODEWITHSUNDEEP
excelvbaexcel-charts
Stephen Callaghan
Stephen Callaghan

Reputation: 57

Making charts appear on individual sheets

Charts are appearing one on top of the other on one sheet instead of on individual sheets in the workbook

My first attempt as using VBA to produce charts. I have 17 sheets in my workbook and want to produce charts on all but sheet 1 (AllData). I'm getting a basic chart for all 16 sheets (haven't managed to workout how to put the sheet name as the title yet) but they are all appearing one on top of the other on sheet 2. My aim is to have them appearing just below lastrow.offset(3) on the correct sheets. I'd welcome any advice...

Sub Macro33()
Application.ScreenUpdating = True
Dim sh As Worksheet
Dim lastRow As Long
Dim ActiveWorksheet As Long
Dim ThisWorksheet As Long
Dim N As Integer
Dim rng As range
Dim cell As range
Dim r As range
Dim j As Integer
Dim x As Integer
x = Sheets.Count

For N = 2 To x

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
range("I1:I" & lastRow).Select
ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Sheets(N).range("I2:I" & lastRow)
ActiveChart.Axes(xlCategory).Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveChart.FullSeriesCollection(1).XValues = Sheets(N).range("J2:J" & lastRow)


Application.ScreenUpdating = False


Next N

range("A1").Select
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 60

Answers (1)

Tim Williams
Tim Williams

Reputation: 166256

Here's how you could do it:

Sub Macro33()

    Dim N As Long, cht As Chart, lastRow, s As Series
    
    For N = 2 To ThisWorkbook.Sheets.Count
        With ThisWorkbook.Sheets(N)
            
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            Debug.Print .Parent.Name, .Name, lastRow '<<<<<<<<
            
            Set cht = .Shapes.AddChart2(227, xlLineMarkers).Chart
            'remove any auto-added series so we can start fresh
            Do While cht.SeriesCollection.Count > 0
                cht.SeriesCollection(1).Delete
            Loop
            
            'add series and set source
            Set s = cht.SeriesCollection.NewSeries
            s.XValues = .Range("J2:J" & lastRow)
            s.Values = .Range("I2:I" & lastRow)
            
            'position the chart's parent ChartObject 
            cht.Parent.Top = .Cells(lastRow + 3, 1).Top
            cht.Parent.Left = .Cells(lastRow + 3, 1).Left
        
        End With
    Next N

End Sub

Upvotes: 2

Related Questions