View Single Post
  #7   Report Post  
Tom9283
 
Posts: n/a
Default

yes, I only Have 1 chart. Thanks a bunch!

"Rowan" wrote:

Tom

I'm not sure about the best way to identify the chart you just created. If
you run the code like this though it will reposition any chart on the sheet.
So if you only have one chart per worksheet then you should be OK

Dim dataSheet As Worksheet
Dim i As Integer
Set dataSheet = ActiveSheet

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.Name = "myChart"
ActiveChart.SetSourceData Source:=dataSheet.Range("B4:B13,D4:D13"), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:=dataSheet.Name
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Weekly Return"
End With
ActiveChart.HasLegend = False
For i = 1 To dataSheet.Shapes.Count
If Left(dataSheet.Shapes(i).Name, 5) = "Chart" Then
dataSheet.Shapes(i).IncrementLeft -228.75
dataSheet.Shapes(i).IncrementTop 18#
End If
Next i
Etc

Rowan


"Tom9283" wrote:

Awesome. Thank you so much.

Now there is only one problem left. The problem is that it doesn't
reposition the chart. It's because of this "chart 8" in the code below. How
can I change that so it selects the chart that was just created. If it's too
complicated just let me know, as it's really just a minor thing.

Thanks again.

ActiveSheet.Shapes("Chart 8").IncrementLeft -228.75
ActiveSheet.Shapes("Chart 8").IncrementTop 18#


"Rowan" wrote:

You nearly had it. Try
ActiveChart.Location Whe=xlLocationAsObject, Name:=dataSheet.Name

Regards
Rowan