Changing Positions of the Chart When Creating Multiple Charts Automatically via Vbasic in Excel 2007
- by McVey
I am creating a new chart for each row of data in an Excel spreadsheet. I have the Vbasic working properly, but I want to change the position of the chart on the sheet that is added for each row.
Below is my code, what do I need to do to change the position of the chart on the page automatically? Ideally, I would like it to be in the upper left hand corner of each sheet.
Sub DrawCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long
Set Ws = ThisWorkbook.Worksheets("Sheet1")
LastRow = Ws.Range("A65536").End(xlUp).Row
For CurrRow = 2 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs.Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xl3DColumnClustered
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8"
.SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2"
.SeriesCollection(1).XValues = "Sheet1!R1C3:R1C8"
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 1
.Axes(xlValue).MajorUnit = 0.2
.SetElement (msoElementDataLabelShow)
.SetElement (msoElementLegendNone)
.Location Where:=xlLocationAsObject, Name:=NewWs.Name
End With
Next CurrRow
End Sub
Any help is appreciated.
