J
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
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