Prev: Tab Selection
Next: transposing data in excel worksheet
From: James8309 on 18 Jul 2008 01:21
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: Joel on 18 Jul 2008 02:02
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
>
From: Madiya on 18 Jul 2008 02:05
On Jul 18, 10:21 am, James8309 <jaedong1...(a)gmail.com> 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
 | 
Pages: 1
Prev: Tab Selection
Next: transposing data in excel worksheet