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
Bookmarks