CODEWITHSUNDEEP
excelvba
Sam Daly
Sam Daly

Reputation: 51

Adding multiple charts to a single worksheet in VBA

I'm currently trying to add some charts to a worksheet in Excel. I'm looping through some data and I want to create a pie chart for each set of data and I want to add all of those charts to the same worksheet in order to create a sort of dashboard feel

This is the code I currently have and its adding the charts to new sheets everytime

For f = 2 to G -1
    Column = w + (f (f-2))
    Set Rng = Worksheets("Dashboard info").Range(Worksheets("Dashboard info").Cells(1, Column -1), Worksheets("Dashboard info").Cells(Worksheets("Dashboard info").Cells(Rows.Count, Column).End(xlUp).Row, Column))
    ChartTitle = Worksheets("Dashboard info").Cells(1, Column).Value
    Dim ws as Worksheet
    Set ws = ActiveSheet
    Sheets.Add after:=Sheets(Sheets.count)
    With ActiveSheet.ChartObjects.Add
        .Chart.SetSourceDate Source:=Rng
        .Chart.ChartType = xlPie
        .Chart.ChartStyle = 253
        .Chart.ChartTitle.Text = ChartTitle
    End with
Next f

This code works, but adds a new sheet for each chart and my goal is to add each chart to a single sheet that already exists in my excel document

How do I do that?

Upvotes: 0

Views: 1287

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Since you do not show us all your code, I cannot test anything, but please, try the next adapted piece of code:

Sub insertCharts()
   'your code...
    '...
    Dim ws As Worksheet, newSh As Worksheet, prevWidth as Double
    Set ws = ActiveSheet
    Set newSh = ws.Parent.Sheets.Add(After:=ws.Parent.Sheets(Sheets.count))
 For f = 2 To g - 1
    Column = w + (f(f - 2))
    Set rng = Worksheets("Dashboard info").Range(Worksheets("Dashboard info").cells(1, Column - 1), Worksheets("Dashboard info").cells(Worksheets("Dashboard info").cells(rows.count, Column).End(xlUp).row, Column))
    ChartTitle = Worksheets("Dashboard info").cells(1, Column).Value
    
    With newSh.ChartObjects.Add
        .Chart.SetSourceDate Source:=rng
        .Chart.ChartType = xlPie
        .Chart.ChartStyle = 253
        .Chart.ChartTitle.text = ChartTitle
        .left = .Range("A1").left + prevWidth
        prevWidth = prevWidth + .width
    End With
 Next f
End Sub

I created a new variable prevWidth, which is incremented after each chart added.

Upvotes: 1

Related Questions