PDA

View Full Version : Accessing X-Axis in a line graph



kkj
03-11-2012, 07:07 AM
Hi Guys,
I am working with line charts and i am successively drawing line charts based on the number of rows. Each row contains one line chart and every time chart is drawn on new worksheet (e.g., if there are 5 rows then on single click 5 line charts will be drawn).
The problem that i am facing is, How to put column headings on the x-axis of each chart.
NOTE: Row heading is already appearing on the y-axis of each chart.
I have also attached my excel sheet for better understanding the problem.....I want DP1, DP2, DP3,....on the x-axis of each line graph.
Here is my code:

Sub LineCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long

Set Ws = ThisWorkbook.Worksheets("Sheet1")

LastRow = Ws.Range("A65536").End(xlUp).Row
For CurrRow = 2 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs.Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlLine
.SeriesCollection.NewSeries

.SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8"
.SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2"
.Location Where:=xlLocationAsObject, Name:=NewWs.Name
End With
Next CurrRow
End Sub

Aflatoon
03-12-2012, 06:31 AM
Add this to your With cht... code:
.SeriesCollection(1).XValues = "=" & Ws.Name & "!R1C3:R1C8"