Problems with Macro that Copies Graphs

[tcolan]

Hello,

I am trying to create a macro in Excel that will copy a graph on the current worksheet and paste it in a particular area on another worksheet (all within the same workbook).

I receive the following VB error:
Run-time error '1004': Unable to get the ChartObjects property of the Worksheet class.

Is there something that I am doing wrong or some way I need to call the new copied graph when I paste it?

Thanks so much for your help,
Tom


Here is the Macro code:

Sub COPY_and_PASTE_GRAPH()
'
' COPY_and_PASTE_GRAPH Macro
' Keyboard Shortcut: Ctrl+g
'
ActiveSheet.ChartObjects("Chart 13&quot.Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows("SAMPLE Program Summary Status Roll-Up_V1.1.xls&quot.Activate
Sheets("MASTER PROGRAM STATUS&quot.Select
Range("H26&quot.Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 96&quot.Activate
ActiveSheet.Shapes("Chart 96&quot.IncrementLeft 7.5
ActiveSheet.Shapes("Chart 96&quot.IncrementTop 3#
End Sub

 

[SkipVought]

tc,

Looks like you might have recorded a macro?

How many chart objects do you have on the source sheet?

What are you trying to do -- from beginning to end?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tcolan]

Thanks for the response.

A. I did record a macro.
B. I have 4 charts/graphs on the source sheet.
C. I am trying to create a macro that will:
1. Delete the 4 graphs from the destination sheet.
2. Copy the 4 graphs from the source sheet.
3. Paste the 4 graphs in the destination sheet in specific areas so they can be printed out as a report.

You may ask the question why I just don't leave the copies on the destination sheet alone since excel will change the graphs according to the source data. I currently have the graphs next to the actual data on the source sheet. They require some minor manual interaction (such as verifying the data ranges and making sure all of the labels are viewable since the graphs have to be a certain size on the destination sheet) I would rather make these changes on the source sheet (next to the data) and then just copy them over to the new sheet. (This is a monthly report that changes)

If this is possible and you have any hints, that would be great and I could eliminate the re-occurring manual cut & paste steps.

Thanks,
Tom

 

[SkipVought]

First, you don't have to cut 'n' paste. Each chart has a location property that is a sheet & cell reference.

Second if we just relocate the charts, then no need to delete etc, right?

Moving charts from Sheet1 to Sheet2...

Sub MoveCharts()
    Application.ScreenUpdating = False
    With Sheet1
        For Each co In .ChartObjects
            co.Chart.Location Where:=xlLocationAsObject, Name:="Sheet2"
        Next
    End With
    With Sheet2
        i = 1
        For Each co In .ChartObjects
            With co.ShapeRange
                .Top = i * 100
                .Left = i * 100
            End With
            i = i + 1
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Just an example of positioning




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884