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

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
_______


"James8309" wrote in message
...
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

On Jul 19, 1:27*am, "Jon Peltier"
wrote:
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
_______

"James8309" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Thanks alot mate! :D Have a good week!