Hi there,
I have a spreadsheet that collates a list of data from week order and puts them in a graph. I have a macro created that basically does the graph, it works fine in 2003 but not in office 2007. I get an error "Run Time Error '1004' Application-defined or object-defined error, then when I click the debug button it highlights "ActiveSheet.ChartObjects.Delete". I'm not sure if when its trying to create a new graph its attempting to delete the old one and isnt deleting it? The full code for the macro is below:
Sub updateStuff()
'Macro by Paul Craddy - IT
Dim searchResult As Range 'Result of the find operation - to check if it has worked or not
'Shut off screen updating to avoid confusing user
Application.ScreenUpdating = False
'Check the chart already exists, and delete it if so
If SheetExists("OTIF Chart") Then
Application.DisplayAlerts = False
Sheets("OTIF Chart").Delete
Application.DisplayAlerts = True
End If
'Copy the headings to the data sheet
Sheets("OTIF's").Select
Range("B4:N4").Select
Selection.Copy
Sheets("Graph").Select
Range("B1").Select
ActiveSheet.Paste
'Delete any existing data
Range("a2:l8192").Clear
Sheets("Macros").Select
Range("D25").Value = Application.InputBox(prompt:="Number of weeks to plot", Type:=1, Default:="52", Title:="WEEKS TO PLOT")
GoTo LastDate:
GraphDate:
Sheets("Macros").Select
Range("D24").Select
ActiveSheet.Paste
Range("D24").Value = Application.InputBox(prompt:="Enter last week to plot", Type:=2, Default:=Range("D24").Value, Title:="LAST TO PLOT")
intWeeksToPlot = Range("D25").Value
intWeeksToPlotNeg = intWeeksToPlot - intWeeksToPlot * 2
strDateToFind = Range("D24").Value
strXAxisTitle = Range("F26").Value
strYAxisTitle = Range("F27").Value
strChartTitle = Range("F24").Value
strChartSubTitle = Range("F25").Value
Sheets("OTIF's").Select
Set searchResult = Cells.Find(what:=strDateToFind)
If searchResult Is Nothing Then
MsgBox "Date not found!", vbExclamation, "Error!"
GoTo EndCode:
Else
Cells.Find(what:=strDateToFind).Select
End If
Selection.Offset(intWeeksToPlotNeg + 1, 0).Select
Selection.Resize(intWeeksToPlot + 1, 14).Select
Selection.Copy
Sheets("Graph").Select
Range("A2").Select
ActiveCell.PasteSpecial xlPasteValues
Columns("B").Delete
Columns("L").Delete
'Format date values as dates to enure proper display
Columns("A:A").Select
Range("A28").Activate
Selection.NumberFormat = "dd mmm yyyy"
'Paste in the dummy target values
Range("M1").Select
ActiveCell.FormulaR1C1 = "Target 2003"
Range("M2").Select
ActiveCell.FormulaR1C1 = "99.75"
Range("M2").Select
Selection.Copy
Range("M2:M54").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.ChartObjects.Delete < ERRORS AT THIS POINT
Range("B2").Select
Selection.Resize(intWeeksToPlot + 1, 11).Select
ActiveSheet.ChartObjects.Add(0, 0, 700, 500).Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("B1:B52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("C1:C52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("D1:D52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("E1:E52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("F1:F52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("G1:G52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("H1:H52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("I1:I52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("J1:J52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("K1:K52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("L1:L52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlLine
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = xlSecondary
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).MarkerStyle = xlNone
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Smooth = False
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("M1:M52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlLine
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = xlSecondary
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).MarkerStyle = xlNone
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Smooth = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Position = xlLegendPositionBottom
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 50
.HasSeriesLines = False
End With
With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = 0
.MaximumScale = 10
End With
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = strChartTitle & Chr(13) & strChartSubTitle
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Caption = strXAxisTitle
ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Caption = strYAxisTitle
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = 97
.MaximumScale = 100
End With
ActiveChart.SeriesCollection(1).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(2).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(3).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(4).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(5).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(6).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(7).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(8).XValues = "=Graph!C1"
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 4
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.Location xlLocationAsNewSheet, "OTIF Chart"
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(11).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
'ActiveChart.SeriesCollection(12).Select
'With Selection.Border
' .ColorIndex = 1
' .Weight = xlThin
' .LineStyle = xlDashDot
'End With
ActiveChart.SeriesCollection(12).Select
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With
GoTo EndCode
LastDate:
Sheets("OTIF's").Select
'Goto first date search value
Range("R7").Select
Checker4:
'Search for latest date
If CDate(ActiveCell.Value) >= Date Or ActiveCell.Value = "" Then
GoTo Found4
End If
ActiveCell.Offset(1, 0).Select
GoTo Checker4
Found4:
'Select the row we have found the date on
ActiveCell.Offset(0, -17).Select
Selection.Copy
GoTo GraphDate
EndCode:
Application.ScreenUpdating = True
End Sub
Thank you so much for your help, this forum is fantastic.
I have a spreadsheet that collates a list of data from week order and puts them in a graph. I have a macro created that basically does the graph, it works fine in 2003 but not in office 2007. I get an error "Run Time Error '1004' Application-defined or object-defined error, then when I click the debug button it highlights "ActiveSheet.ChartObjects.Delete". I'm not sure if when its trying to create a new graph its attempting to delete the old one and isnt deleting it? The full code for the macro is below:
Sub updateStuff()
'Macro by Paul Craddy - IT
Dim searchResult As Range 'Result of the find operation - to check if it has worked or not
'Shut off screen updating to avoid confusing user
Application.ScreenUpdating = False
'Check the chart already exists, and delete it if so
If SheetExists("OTIF Chart") Then
Application.DisplayAlerts = False
Sheets("OTIF Chart").Delete
Application.DisplayAlerts = True
End If
'Copy the headings to the data sheet
Sheets("OTIF's").Select
Range("B4:N4").Select
Selection.Copy
Sheets("Graph").Select
Range("B1").Select
ActiveSheet.Paste
'Delete any existing data
Range("a2:l8192").Clear
Sheets("Macros").Select
Range("D25").Value = Application.InputBox(prompt:="Number of weeks to plot", Type:=1, Default:="52", Title:="WEEKS TO PLOT")
GoTo LastDate:
GraphDate:
Sheets("Macros").Select
Range("D24").Select
ActiveSheet.Paste
Range("D24").Value = Application.InputBox(prompt:="Enter last week to plot", Type:=2, Default:=Range("D24").Value, Title:="LAST TO PLOT")
intWeeksToPlot = Range("D25").Value
intWeeksToPlotNeg = intWeeksToPlot - intWeeksToPlot * 2
strDateToFind = Range("D24").Value
strXAxisTitle = Range("F26").Value
strYAxisTitle = Range("F27").Value
strChartTitle = Range("F24").Value
strChartSubTitle = Range("F25").Value
Sheets("OTIF's").Select
Set searchResult = Cells.Find(what:=strDateToFind)
If searchResult Is Nothing Then
MsgBox "Date not found!", vbExclamation, "Error!"
GoTo EndCode:
Else
Cells.Find(what:=strDateToFind).Select
End If
Selection.Offset(intWeeksToPlotNeg + 1, 0).Select
Selection.Resize(intWeeksToPlot + 1, 14).Select
Selection.Copy
Sheets("Graph").Select
Range("A2").Select
ActiveCell.PasteSpecial xlPasteValues
Columns("B").Delete
Columns("L").Delete
'Format date values as dates to enure proper display
Columns("A:A").Select
Range("A28").Activate
Selection.NumberFormat = "dd mmm yyyy"
'Paste in the dummy target values
Range("M1").Select
ActiveCell.FormulaR1C1 = "Target 2003"
Range("M2").Select
ActiveCell.FormulaR1C1 = "99.75"
Range("M2").Select
Selection.Copy
Range("M2:M54").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.ChartObjects.Delete < ERRORS AT THIS POINT
Range("B2").Select
Selection.Resize(intWeeksToPlot + 1, 11).Select
ActiveSheet.ChartObjects.Add(0, 0, 700, 500).Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("B1:B52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("C1:C52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("D1:D52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("E1:E52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("F1:F52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("G1:G52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("H1:H52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("I1:I52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("J1:J52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("K1:K52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlColumn
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnStacked
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("L1:L52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlLine
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = xlSecondary
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).MarkerStyle = xlNone
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Smooth = False
ActiveChart.SeriesCollection.Add Source:=Worksheets("Graph").Range("M1:M52")
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Type = xlLine
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = xlSecondary
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).MarkerStyle = xlNone
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Smooth = False
ActiveChart.HasLegend = True
ActiveChart.Legend.Position = xlLegendPositionBottom
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 50
.HasSeriesLines = False
End With
With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = 0
.MaximumScale = 10
End With
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Caption = strChartTitle & Chr(13) & strChartSubTitle
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Caption = strXAxisTitle
ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Caption = strYAxisTitle
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = 97
.MaximumScale = 100
End With
ActiveChart.SeriesCollection(1).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(2).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(3).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(4).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(5).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(6).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(7).XValues = "=Graph!C1"
ActiveChart.SeriesCollection(8).XValues = "=Graph!C1"
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 4
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.Location xlLocationAsNewSheet, "OTIF Chart"
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(11).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
'ActiveChart.SeriesCollection(12).Select
'With Selection.Border
' .ColorIndex = 1
' .Weight = xlThin
' .LineStyle = xlDashDot
'End With
ActiveChart.SeriesCollection(12).Select
With Selection.Border
.ColorIndex = 10
.Weight = xlThin
.LineStyle = xlContinuous
End With
GoTo EndCode
LastDate:
Sheets("OTIF's").Select
'Goto first date search value
Range("R7").Select
Checker4:
'Search for latest date
If CDate(ActiveCell.Value) >= Date Or ActiveCell.Value = "" Then
GoTo Found4
End If
ActiveCell.Offset(1, 0).Select
GoTo Checker4
Found4:
'Select the row we have found the date on
ActiveCell.Offset(0, -17).Select
Selection.Copy
GoTo GraphDate
EndCode:
Application.ScreenUpdating = True
End Sub
Thank you so much for your help, this forum is fantastic.