Simplest Question

[James8309]

Hi everyone

I have this macro below that creates charts automatically and it works
so far with no problems. I have 4 sheets Sheets("V","$","RU","Chart")
Currently all the charts gets created below the actual data sheet
"$".

Q: How do I alter or add to this macro in order to make the chart in
sheets("Chart") instead of sheets("$") ?

Thanks alot for your help.

regards,

James


Sub Graph()

Dim L As Single, T As Single, W As Single, H As Single
Dim i As Long, cnt As Long
Dim X As Long, xx As Long, lastRow As Long
Dim gap As Single
Dim co As ChartObject
Dim cht As Chart
Dim sr As Series
Dim rng As Range, cell As Range
'Dim ws As Worksheets

' ( Preset Options )

gap = 12
W = 320
H = 300
X = 4

Set ws = ActiveWorkbook.Worksheets("$")

Set rng = ws.Range("A6")
lastRow = rng.End(xlDown).Row
If lastRow < ws.Rows.Count Then
Set rng = rng.Resize(lastRow - rng.Row + 1, 1)
End If

' ( Deletion of previous charts )
' ws.ChartObjects.Delete


L = gap
T = rng.Cells(rng.Count + 2).Top + gap

For Each cell In rng

Set cht = ws.ChartObjects.Add(L, T, W, H).Chart
With cht
.ChartArea.Font.Size = 10
.ChartType = xlLine
.ChartArea.Interior.ColorIndex = 15
.ChartArea.Interior.PatternColorIndex = 1
.ChartArea.Interior.Pattern = 1
.ChartArea.Border.Weight = 1
.ChartArea.Border.LineStyle = -1



Set sr = .SeriesCollection.NewSeries
sr.Name = cell.Value
sr.XValues = ws.Range("B5:AQ5")
sr.Values = cell.Offset(, 1).Resize(,
ws.Range("B5:AQ5").Columns.Count)
'.Address(,,xlr1c1)
sr.Border.ColorIndex = 3
sr.Border.Weight = xlMedium
sr.Border.LineStyle = xlContinuous

.HasTitle = True
.ChartTitle.Text = "=" & cell.Address(, , xlR1C1, True)
.HasLegend = False 'Legend part

.PlotArea.Border.ColorIndex = 16
.PlotArea.Border.Weight = xlThin
.PlotArea.Border.LineStyle = xlContinuous
.PlotArea.Interior.ColorIndex = 1
.PlotArea.Interior.PatternColorIndex = 1
.PlotArea.Interior.Pattern = xlSolid

End With

L = L + W + gap
xx = xx + 1
If xx = X Then
xx = 0
L = gap
T = T + H + gap

End If
Next


End Sub

 

[Jon Peltier]

This line:

Set ws = ActiveWorkbook.Worksheets("$")

defines sheet '$' as the target for the procedures efforts.

- Jon

 

[James8309]

This line:

    Set ws = ActiveWorkbook.Worksheets("$")

defines sheet '$' as the target for the procedures efforts.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

























- Show quoted text -

Thanks alot mate! Have a good week!