PDA

View Full Version : VBA codes to draw charts with data from multiple sheets



tcambridge
06-17-2014, 12:14 PM
:help

Hi,
I need VBA codes that will creat a 3-D line chart by collecting sale data number from the same location on 12 Excel sheets. Example

Sheet1- Jan14:
Location Sale Beg Inv. End Inv.
San Francisco,CA 36 55 19
San Diego, CA 18 36 18
Houston, TX 53 81 28

Sheet2- Feb14:
Location Sale Beg Inv. End Inv.
San Francisco,CA 25 55 19
San Diego, CA 18 36 18
Houston, TX 53 81 28

So on and so on for all 12 months.

I need VBA code that draw 3-D Line chart for each sale location on 12 months. The only problem I have it that each months have a spreadsheet. I greatly appreciate suggestions and inputs.

EirikDaude
06-18-2014, 05:55 AM
This is how I solved a somewhat similar problem in one of my worksheets, it shouldn't be too hard to modify it for your use:

Sub settHovudgrafData()
Dim xverdiar As String, antMinutt As String, kumProsent As String, r As Range, c As Chart
Dim xverdiar2 As String, antMinutt2 As String, kumProsent2 As String, r2 As Range, c2 As Chart

Set r = Data.Range("A3"): Set r = Range(r, r.Offset(1048573, 2).End(xlUp))
Set r2 = Data_antal.Range("A3"): Set r2 = Range(r2, r2.Offset(1048573, 2).End(xlUp))
Set c = Grafar.ChartObjects("Minutt").Chart: Set c2 = Grafar.ChartObjects("Antal").Chart

xverdiar = "=" + r.Columns(1).Address(external:=True)
antMinutt = "=" + r.Columns(2).Address(external:=True)
kumProsent = "=" + r.Columns(3).Address(external:=True)
xverdiar2 = "=" + r2.Columns(1).Address(external:=True)
antMinutt2 = "=" + r2.Columns(2).Address(external:=True)
kumProsent2 = "=" + r2.Columns(3).Address(external:=True)

c.SeriesCollection("Stanstider").Values = antMinutt
c.SeriesCollection("Stanstider").XValues = xverdiar
With c.SeriesCollection("Kumulativ prosent")
.Values = kumProsent
.XValues = xverdiar
End With
c2.SeriesCollection("Stanstider").Values = antMinutt2
c2.SeriesCollection("Stanstider").XValues = xverdiar2
With c2.SeriesCollection("Kumulativ prosent")
.Values = kumProsent2
.XValues = xverdiar2
End With
End Sub

tcambridge
06-18-2014, 08:08 AM
11842Thank you Eirik. However, somehow I keep getting error message for your codes. I attached sample here for you to see if you can help.

EirikDaude
06-19-2014, 12:58 AM
Yeah, that is code I used for my sheet, so you'll need to rename the chart series, make a function for looping through the sheets, etc. It was meant more to show the concept of how I'd approach the problem and maybe some useful function calls, rather than be a complete solution.

I'll see if I can find the time to have a look at your workbook later today, though no promises :P