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

from

Set ws = ActiveWorkbook.Worksheets("$")

to

Set ws = ActiveWorkbook.Worksheets("Chart")

"James8309" wrote:

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 18, 10:21*am, James8309 wrote:
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

Hi,
Replace following line.
Set cht = ws.ChartObjects.Add(L, T, W, H).Chart
with
Set cht = sheets("Chart").ChartObjects.Add(L, T, W, H).Chart

Regards,
Madiya