The following code is designed to copy 2 charts from one sheet and pastes
them as a picture onto another sheet.

How do I assign the pasted picture to a variable so that I can manipulate it
(i.e. change the location, width, etc)

The following code does not work (throws an "Object Required" error on 'Set
myObj =')

Any ideas?

Sub testMe3()
Worksheets("Graph").Select
ActiveWindow.Visible = False
Windows("Report_Aug06.xls").Activate
Sheets("Graph").Shapes.Range(Array("Chart 40", "Chart 43")).Select

Selection.Copy

Worksheets("Instructions").Select
Range("A97").Select

Dim myObj As Shape 'As Object

Set myObj = ActiveSheet.PasteSpecial(Format:="Picture (Enhanced
Metafile)", Link:=False, DisplayAsIcon:=False)
Debug.Print TypeName(myObj) & " - " & myObj
End Sub

Hi,

The pastespecial does not return a object reference. So use the shape
collection to rename the latest item.

ActiveSheet.PasteSpecial _
Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Name = "NewName"

Cheers
Andy

Andibevan wrote:
The following code is designed to copy 2 charts from one sheet and pastes
them as a picture onto another sheet.

How do I assign the pasted picture to a variable so that I can manipulate it
(i.e. change the location, width, etc)

The following code does not work (throws an "Object Required" error on 'Set
myObj =')

Any ideas?

Sub testMe3()
Worksheets("Graph").Select
ActiveWindow.Visible = False
Windows("Report_Aug06.xls").Activate
Sheets("Graph").Shapes.Range(Array("Chart 40", "Chart 43")).Select

Selection.Copy

Worksheets("Instructions").Select
Range("A97").Select

Dim myObj As Shape 'As Object

Set myObj = ActiveSheet.PasteSpecial(Format:="Picture (Enhanced
Metafile)", Link:=False, DisplayAsIcon:=False)
Debug.Print TypeName(myObj) & " - " & myObj
End Sub



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info