Run time error with VBA to copy Chart objects from closed workbook

[jessrabbit]

Good morning and Happy Easter,

Please could someone help to find a solution to the run time error 5 (Invalid Procedure call or argument) on this line of the code below?

wsf.ChartObjects("Chart_1").CopyPicture xlScreen, xlP

The whole code is here and is intended to copy specified charts from closed workbooks and paste into the current workbook;

Sub Macro3()
'
' Macro to copy KPI charts from their closed workbook sources into one location.
'
 
' Declare variables
Dim sourceWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim wsf As Worksheet
Dim wst As Worksheet
 
' Set the destination workbook to the active workbook
Set targetWorkbook = ThisWorkbook
 
' Disable screen updating
Application.ScreenUpdating = False
 
' Open the source workbook
Set sourceWorkbook = Workbooks.Open("xyz123.xlsx")
 
'Set the worksheet that you want to copy data from
Set wsf = sourceWorkbook.Sheets("KPIs")
 
'Set the worksheet that you want to copy data to
Set wst = targetWorkbook.Sheets("KPI Summary")
 
'Copy the Chart to the destination workbook
wsf.ChartObjects("Chart_1").CopyPicture xlScreen, xlP
wst.Range ("c9").Paste
wsf.ChartObjects("Chart_2").CopyPicture xlScreen, xlP
wst.Range ("i9").Paste
wsf.ChartObjects("Chart_3").CopyPicture xlScreen, xlP
wst.Range ("o9").Paste
 
'Close the source workbook without saving changes
sourceWorkbook.Close SaveChanges:=False
 
'Enable screen updating
Application.ScreenUpdating = True
 
'Release object variables to release memory
Set ws = Nothing
Set sourceWorkbook = Nothing
Set targetWorkbook = Nothing
End Sub

 

[jessrabbit]

Hi, I've experimented with a few more options and found the solution which is to use;

wst.Range("c9").PasteSpecial Paste:=Picture

Thank you for looking at this post and hope you have a lovely day.

Jess

 

[Domenic]

The second argument of the CopyPicture method should be xlPicture, for example...

wsf.ChartObjects("Chart_1").CopyPicture xlScreen, xlPicture

Hope this helps!

 

[jessrabbit]

Thank you, very helpful. Kind regards, Jess

 

[Domenic]

You're very welcome, glad I could help.

Cheers!