CODEWITHSUNDEEP
vbaexcelgraph
andrew
andrew

Reputation: 41

Using charts with VBA

I'm trying to generate two charts using VBA. The problem is most examples use ActiveChart but I want multiple charts on multiple sheets. If I inserted a blank chart how do I rename that chart to reference it. I don't want a new chart to be generated each time I run the macro and I want it to be in the sheet. I'm struggling with the code but am assuming it will be something like the code below. I've attached the desired graph (I made this through excel, but I need to do it through VBA).

macro1()
lastrow2 = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
dim chart1 as chart
dim chart2 as chart ' ect
chart1.title  = "test"
chart1.xaxis = sheet1.cell(lastrow2,1)
chart1.yaxis = "manhours"
end sub

enter image description here

using a the record function, i got the code commented below. I tried to change it but i'm still having issues

Sub Macro7()

Dim Chart2 As ChartObject
Dim chartb As Chart
Chart2 = Sheet1.chartb.SeriesCollection(2)
chartb.Select
Formula = "=SERIES(Master!R3C3,Master!R4C1:R18C1,Master!R4C3:R19C3,2)"



'   ActiveChart.SeriesCollection(2).Select
 '  Selection.Formula =_ 
'"=SERIES(Master!R3C3,Master!R4C1:R18C1,Master!R4C3:R19C3,2)"

End Sub

I really just need this formula converted to i can reference my lastrow function and individual sheets

ActiveChart.SeriesCollection(2).Select
Selection.Formula =_ 
"SERIES(sheet1.cells(3,3),sheet1.cells(4,1):sheet1.cells(18,1)_
,sheet1.cells(4,3):sheet1.cells(4,19),2"
' Selection.Formula"_
' =SERIES(Master!R3C3,Master!R4C1:R18C1,Master!R4C3:R19C3,2)"

Upvotes: 0

Views: 199

Answers (1)

andrew
andrew

Reputation: 41

this was what i was trying to do. It declares the sheet name and references an existing chart named chart 1. 

Dim cht As ChartObject
Dim rng As Range

Set cht = Sheets("Master").ChartObjects("Chart 1")
Set rng = Sheets("Master").Range("A4", Range("D4").End(xlDown).Offset(-1))
cht.Chart.SetSourceData Source:=rng
cht.Chart.HasTitle = True
cht.Chart.ChartTitle.Text = "Bird Report - By Cost Code/Activity" ' title

cht.Chart.SeriesCollection(1).Name = "=Master!$B$3"
cht.Chart.SeriesCollection(2).Name = "=Master!$C$3"
cht.Chart.SeriesCollection(3).Name = "=Master!$D$3"

Upvotes: 0

Related Questions