View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.charting
Arlette[_2_] Arlette[_2_] is offline
external usenet poster
 
Posts: 14
Default Problem with excel macro - getting automation error

The offending code is as below:

public sub create_chart()

Dim chart2 As Chart, chart4 As Chart, chart10 As Chart, chart15 As
Chart, chart12 As Chart
Dim chartobj As ChartObject


Windows(Frmselavs.selbuyer).Activate

chartobj = ActiveSheet.ChartObjects

Set chart2 = chartobj.Chart
Set chart4 = chartobj.Chart
Set chart10 = chartobj.Chart
Set chart15 = chartobj.Chart
Set chart12 = chartobj.Chart

chart2 = ActiveSheet.ChartObjects("Chart2")
chart4 = ActiveSheet.ChartObjects("Chart4")
chart10 = ActiveSheet.ChartObjects("Chart10")
chart15 = ActiveSheet.ChartObjects("Chart15")
chart12 = ActiveSheet.ChartObjects("Chart12")

Windows(filename).Activate
Columns("G:G").Activate
Selection.ColumnWidth = 3.43
Columns("N:N").Activate
Selection.ColumnWidth = 3.43


'ACTIONS FOR CHART 2
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart2").Activate
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("A6").Select
ActiveSheet.Paste
ActiveWindow.Visible = False


'ACTIONS FOR CHART 4
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart4").Activate
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("H6").Select
ActiveSheet.Paste
ActiveWindow.Visible = False


'ACTIONS FOR CHART 10
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart10").Select
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("O6").Select
ActiveSheet.Paste


'ACTIONS FOR CHART 15
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart15").Select
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("C25").Select
ActiveSheet.Paste


'ACTIONS FOR CHART 12
Windows(Frmselavs.selbuyer).Activate
Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart12").Select
ActiveChart.ChartArea.Copy
Windows(filename).Activate
Sheets(z).Select
Range("J25").Select
ActiveSheet.Paste


'ALIGN ALL THE CHARTS IN THE SHEET

Dim iChart As Long
Dim nCharts As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim nColumns As Long


dHeight = 217
dWidth = 289


nCharts = ActiveSheet.ChartObjects.count

For iChart = 1 To nCharts

With ActiveSheet.ChartObjects(iChart)

.Activate
.Height = dHeight
.Width = dWidth
.Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth

ActiveChart.Legend.Position = xlLegendPositionBottom

End With

Next

end sub