cancel
Showing results for 
Search instead for 
Did you mean: 

reg work book

former_member185071
Contributor
0 Kudos

Hi Xpertz

Could any one explain me in the following senario

I have a work book which consits of 3 sheets

in that sheet 1 contains one query having A to Z

in the sheet 2 i have another query having 1 to 30

now in sheet 3 i want to display a graph which will show the fields

on x axis -A, B, C , D ,-----k etc.,

on Y axix -- 1,2,3,4,5,6,7,....20 etc.

Hopw can i achieve this any suggestions will appreciate.

Thanx in advance

Regards

R M K

      • No nee to say good answers will get full pointz****

Accepted Solutions (1)

Accepted Solutions (1)

former_member188325
Active Contributor
0 Kudos

You need to have one more sheet (which can be hidden after creation of Chart) with required format and read data from first two sheets(using excel functionality,where data in 3rd sheet is automatically updated from first 2 original sheets) then create chart on this data.

this is the easiest way to do.

Assigning points to useful answers is the way of saying thanks here is sdn.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi RMK,

To avoid a third worksheet, you can name the ranges that you want to use for X and Y axes.

I would need more details to give you the exact VB code for this; but, it would work something like this:

Dim rng as Range

'assume that x values are in row 10

set rng = ThisWorkbook.Sheets("Sheet1").Range("A10:Z10")

rng.Name = "xValues"

'assume that y values are in column C

set rng = ThisWorkbook.Sheets("Sheet2").Range("C5:C31")

rng.Name = "dataValues"

Dim ch As Chart, s As Series

'this assumes that the chart already exists

'and, that it is on a separate chart sheet

Set ch = ThisWorkbook.Sheets("Chart1")

Set s = ch.SeriesCollection(1)

s.Values = Range("dataValues")

s.XValues = Range("xValues")

If the chart is embedded on a worksheet, you need slightly different code:

Dim ws As Worksheet, co As ChartObject

Set ws = ThisWorkbook.Sheets("Comments")

'assumes there is only one embedded chart

'on this worksheet

Set co = ws.ChartObjects(1)

Dim ch As Chart, s As Series

Set ch = co.Chart

Set s = ch.SeriesCollection(1)

s.Values = Range("dataValues")

s.XValues = Range("xValues")

- Pete