Runtime 1004/Error 400

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
I have the following code that takes a snapshot of a graph and drops in on the spreadsheet below the graph for easy visual compare before/after an update. It runs perfectly every time when I step (F8) though the editor. When I use F5 in the editor to run it full speed I get (the unhelpful) "Run-Time Error 1004, Application-define or Object-defined error".. When I run it from a macro-assigned "shape" on the spreadsheet as originally intended I get the infamous (and equally unhelpful) Error 400.

VBA Code:
Sub Copy_Graph()
    Worksheets("Print Plot").ChartObjects("Chart 3").Select
    Selection.Copy
    Range("A30").Select
    Activesheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
End Sub

This code used to run fine from the spreadsheet every time, then over time the error began popping up intermittently (repeated attempts would usually work), now it happens almost every time (repeated attempts not working). I would think the code is OK (maybe not optimal) because it steps through every time in the editor.

Really needs ideas here.

Thanks!!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,926
Office Version
  1. 365
Platform
  1. Windows
Try it without selecting anything, like this...

VBA Code:
Sub Copy_Graph()

    Worksheets("Print Plot").ChartObjects("Chart 3").Copy
    
    ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
    
    With ActiveSheet
        With .Shapes(.Shapes.Count)
            .Left = Range("A30").Left
            .Top = Range("A30").Top
        End With
    End With
    
End Sub

Does this help?
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
I have the following code that takes a snapshot of a graph and drops in on the spreadsheet below the graph for easy visual compare before/after an update. It runs perfectly every time when I step (F8) though the editor. When I use F5 in the editor to run it full speed I get (the unhelpful) "Run-Time Error 1004, Application-define or Object-defined error".. When I run it from a macro-assigned "shape" on the spreadsheet as originally intended I get the infamous (and equally unhelpful) Error 400.

VBA Code:
Sub Copy_Graph()
    Worksheets("Print Plot").ChartObjects("Chart 3").Select
    Selection.Copy
    Range("A30").Select
    Activesheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
End Sub

This code used to run fine from the spreadsheet every time, then over time the error began popping up intermittently (repeated attempts would usually work), now it happens almost every time (repeated attempts not working). I would think the code is OK (maybe not optimal) because it steps through every time in the editor.

Really needs ideas here.

Thanks!!

Try it without selecting anything, like this...

VBA Code:
Sub Copy_Graph()

    Worksheets("Print Plot").ChartObjects("Chart 3").Copy
   
    ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
   
    With ActiveSheet
        With .Shapes(.Shapes.Count)
            .Left = Range("A30").Left
            .Top = Range("A30").Top
        End With
    End With
   
End Sub

Does this help?
Thanks

Note that I'm pasting to the same page as the original chart AND have a bunch of text boxes with various macros assigned (i.e. multiple shapes) that I don't want moved. Also note that some users of my project don't delete old snapshots before creating another (before the error started happening that is).

Does the With .Shapes(.Shapes.Count) by default pick the newest shape or do I need to modify this somewhat?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,926
Office Version
  1. 365
Platform
  1. Windows
Does the With .Shapes(.Shapes.Count) by default pick the newest shape or do I need to modify this somewhat?

Yes, it will always refer to the last shape that you either inserted or pasted onto your worksheet. So my code does the same thing, only it doesn't select anything. If you're still having the same problem, post back and we can try something else.
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,026
Fixed with an easier process than yours...you suggested in another thread that it was timing and to avoid "selecting". Our internal network has slowed down over the previous few months so I suspect you're right in this instance.

However instead moving the shape, I just selected the target location first, then did the copy/paste (I switched to the CopyPicture method instead as it seemed cleaner) and now it runs every time. That said, the network is in light use at the moment so time will tell, but it's promising.

Thanks!!
 
Solution

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,926
Office Version
  1. 365
Platform
  1. Windows
That's great, hope it works out.

Cheers!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,840
Messages
5,766,729
Members
425,375
Latest member
Bradleyckx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top