Simplest Question
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!
|