how to create multiple charts with vba

Question

I need some help.... I have this code in sheet1:

Sheets("kips").Select

Dim i As Integer 'rows
Dim j As Integer 'columns

i = Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To 5
    With ActiveSheet.Shapes.AddChart.Chart
        .Parent.Name = "Chart_" & (j - 1)
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        
    With .SeriesCollection(1)
        '.Name = "=" & ActiveSheet.Name & "!" & _
        'Cells(1, j).Address
        .XValues = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(2, 1), Cells(i, 1)).Address
        .Values = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(2, j), Cells(i, j)).Address
        
        End With
        
    End With
Next j

And I need to add new charts in an other sheet, so I tried to use the same code:

Sheets("sheet2").Select

Dim i As Integer 'rows
Dim j As Integer 'columns

i = Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To 5
    With ActiveSheet.Shapes.AddChart.Chart
        .Parent.Name = "Chart_" & (j - 1)
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        
    With .SeriesCollection(1)
        '.Name = "=" & ActiveSheet.Name & "!" & _
        'Cells(1, j).Address
        .XValues = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(2, 1), Cells(i, 1)).Address
        .Values = "=" & ActiveSheet.Name & "!" & _
        Range(Cells(2, j), Cells(i, j)).Address
        
        End With
        
    End With
Next j

Is the same model of the tabel, but I need to put this in another sheet, here is my tabel:

What I am doing wrong? Thank you

Be careful with using .Select for a worksheet and then using Activesheet. Selecting a sheet does not necessarily make it active. Use Activate instead. As an alternative, you shouldn't select or activate a worksheet at all. Create a variable object for the worksheet and assign it: Dim ws As Worksheet; Set ws = ThisWorkbook.Sheets("kips"). Then use the reference to ws and you guarantee which sheet you're referencing, which is always safer. — PeterT
– PeterT, Commented Feb 22, 2021 at 16:39
but how I reference a ws? Because I need to do 4 graphs with this table, so, how I can make a code who can choice separat this ws in 4 graphs? — Isabella Schneider
– Isabella Schneider, Commented Feb 22, 2021 at 16:44

Tim Williams · Accepted Answer · 2021-02-22 17:04:56Z

When working with sheets it's always a good idea to create sheet variables, assign them to the sheets you're working with, and then use those variables instead of referring to sheets via their name, or "Select sheet >> ActiveSheet" etc

Dim i As Long 'use Long
Dim j As Long
Dim wsCht As Worksheet, wsData As Worksheet

Set wsData = ActiveSheet
Set wsCht = ThisWorkbook.Sheets("Sheet2")

i = wsData.Cells(Rows.Count, 1).End(xlUp).Row

For j = 2 To 5
    With wsCht.Shapes.AddChart.Chart
        .Parent.Name = "Chart_" & (j - 1)
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        
        With .SeriesCollection(1)
            '.Name = "=" & wsData.Name & "!" & wsdata.Cells(1, j).Address
            .XValues = "='" & wsData.Name & "'!" & _
                   wsData.Range(wsData.Cells(2, 1), wsData.Cells(i, 1)).Address
            .Values = "='" & wsData.Name & "'!" & _
                   wsData.Range(wsData.Cells(2, j), wsData.Cells(i, j)).Address
        
        End With
        
    End With
Next j

Collectives™ on Stack Overflow

how to create multiple charts with vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related