How do I make my macro enlarge the chart it created?

[_Nickel_]

I recorded a macro that created a chart that I then enlarged before stopping the recording.
The problem is that when I run the macro it wants to enlarge the chart I created during recording (chart 35) but not the one it just created.
If I change the number of the chart in Visual Basic to the number I know the chart macro is going to create is going to have then it works, but what's the point of the macro if I have to edit it every time before running it?
Any help appreciated!
Thx

 

[Mudface]

You could use code like this, which adds the embedded chart and sizes it in one line, then just sets the chart type and sourcedata range. Take a look at the VBA help entry for ChartObject for more info:-

Sub CreateandSizeChart()
Dim chtobj As ChartObject

' Add embedded chart,
Set chtobj = ActiveSheet.ChartObjects.Add(Left:=100, Top:=30, Width:=300, Height:=300)

' Set source range and chart type
With chtobj
    .Chart.SetSourceData ActiveSheet.Range("A1:B7")
    .Chart.ChartType = xlColumnClustered
End With

'change size, not really necessary as you've already specified it, just added for info
'chtobj.Height = 250
'chtobj.Width = 350

End Sub

 

[_Nickel_]

I am pretty much a complete newbie concerning Visual Basic so I don't really know where and how to use your code, Mudface.
This is my code, maybe you can tell me from it what I need to change:

Range("A27").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-23]C:R[-4]C)"
Selection.AutoFill Destination:=Rows("27:27"), Type:=xlFillDefault
Rows("27:27").Select
ActiveWindow.ScrollColumn = 1
Range("B30").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Tabelle1").Range("B30")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Tabelle1!R25"
ActiveChart.SeriesCollection(1).Values = "=Tabelle1!R27"
ActiveChart.SeriesCollection(1).Name = "=""Messungen"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Tabelle1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Messungen"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Auftragsnummer"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Dicke"
End With
ActiveSheet.Shapes("Diagramm 35").IncrementLeft -124.5
ActiveSheet.Shapes("Diagramm 35").IncrementTop 254.25
Windows("MakroAlpha.xls").SmallScroll Down:=20
ActiveSheet.Shapes("Diagramm 35").ScaleWidth 25.09, msoFalse, _
msoScaleFromTopLeft
Windows("MakroAlpha.xls").ScrollColumn = 1
End Sub


What I think is wrong is the reference to "Diagramm 35" (="chart 35" in German) but I don't know what I need to change it into.

 

[Mudface]

Try the following, I haven't been able to check it thoroughly for obvious reasons, but it should work OK. Change the numbers given in brackets for the chart's Left, Top, Width and Height to suit: -

Sub CreateandSizeChart()
Dim chtobj As ChartObject

Range("A27").FormulaR1C1 = "=AVERAGE(R[-23]C:R[-4]C)"
Range("A27").AutoFill Rows("27:27")

' Add embedded chart,
Set chtobj = ActiveSheet.ChartObjects.Add(Left:=100, Top:=30, Width:=300, Height:=300)

With chtobj.Chart
    .SetSourceData Sheets("Tabelle1").Range("B30")
    .SeriesCollection.NewSeries
    .SeriesCollection(1).XValues = "=Tabelle1!R25"
    .SeriesCollection(1).Values = "=Tabelle1!R27"
    .SeriesCollection(1).Name = "=""Messungen"""
    .HasTitle = True
    .ChartTitle.Characters.Text = "Messungen"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Auftragsnummer"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Dicke"
End With

End Sub