Thread: Pasting (charts) error from 2003 to 2007 (and 2010)

Hi,


I have a macro that I wrote in 2003 but gives me an error (Run-time error '1004' Paste method fail) when I try to run it in in 2007 or 2010. I'm really not sure why. I'm basically creating a bunch of charts that comes from a varying amount of data. I have also attached a workbook so you can see what I am doing, The part where the error is thrown is wsCharts.Paste. Please any help is greatly appreciated.

Variables
'wsCharts and wsData are the two worksheets
' conSpacing is the constant for the spacing between graphs (20 rows)
' this is based on how excel creates graphs on my machine, it changes if
' excel isn't full screen
' i and j are just counters
' LastRow is the final data entry in the table in column C

    Dim wsCharts As Worksheet
    Set wsCharts = Worksheets("Charts")
    'Dim wsCUM As Worksheet
    'Set wsCUM = Worksheets("***")
    Const conSpacing = 20, conStartInRow = 6
    'Dim i, j, LastRow As Long
    
'Delete all existing charts on the page

    wsCharts.ChartObjects.Delete

'Find the last data entry in column C

    With wsCUM
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    
'Add a blank chart
'Linemarkers is the chart type
'set a dummy data range, and tell the graph to plot by rows
'Put the chard in the charts worksheet
'Tell the sheet it has a title
'Set the x and y axis as Date and Hours
'Cut the sample chart

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SetSourceData Source:=wsCUM.Range("E6:BE10"), PlotBy:= _
        xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:=wsCharts.Name
    With ActiveChart
        .HasTitle = True
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
        
         End With
         
         With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .BaseUnitIsAuto = True
        .MajorUnit = 14
        .MajorUnitScale = xlDays
        .MinorUnit = 7
        .MinorUnitScale = xlDays
        .Crosses = xlAutomatic
        .AxisBetweenCategories = True
        .ReversePlotOrder = False
    End With
    ActiveChart.Axes(xlCategory).Select
     With Selection.TickLabels
        .Alignment = xlCenter
        .Offset = 100
        .ReadingOrder = xlContext
        .Orientation = xlUpward
    End With
    
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MajorTickMark = xlOutside
        .MinorTickMark = xlInside
        .TickLabelPosition = xlNextToAxis
    End With
  
  With ActiveSheet.ChartObjects
  .Width = 650
  End With
  
  
    wsCharts.ChartObjects.Cut
    
'For 1 to the number of groups of four

    For i = 1 To (LastRow - conStartInRow) / 4
    
'If it is the first i, select cell A1, else select the cell according to the spacing
        
        If i = 1 Then
            wsCharts.Cells(1, 1).Select
        Else
            wsCharts.Cells((i - 1) * conSpacing, 1).Select
        End If
        
'Paste the chart to this location

        wsCharts.Paste
        
'Set the series 1 to 4, according to the 4 data ranges in the table
        
        For j = 0 To 3
    
            ActiveChart.SeriesCollection(j + 1).XValues = "=" & wsCUM.Name & "!R6C5:R6C57"
            
            ActiveChart.SeriesCollection(j + 1).Values = "=" & wsCUM.Name & "!R" & _
                                (i * 4) + (conStartInRow / 2) + j & "C5:" _
                                & "R" & (i * 4) + (conStartInRow / 2) + j & "C57"
        
            ActiveChart.SeriesCollection(j + 1).Name = "=" & wsCUM.Name & "!R" & _
                                    (i * 4) + (conStartInRow / 2) + j & "C3:" _
                                    & "R" & (i * 4) + (conStartInRow / 2) + j & "C4"
            
        Next

'Give the chart a title of the first label of the data series

        With ActiveChart
            .ChartTitle.Characters.Text = wsCUM.Cells(i * 4 + conStartInRow, 3)
        End With
    Next

I could not get the code to fail.

You might try referencing the 1st chartobject rather than the complete collection.

    wsCharts.ChartObjects(1).Cut

I don't know the exact reason but the object model for charting and shapes was rewritten for xl2007. So things not working as they did in xl2003 is just to be expected and worked around, if possible.