Pasting OleObjects

[Wizz]

What is the appropriate code/command to paste a Form from the Excel ControlsToolBar?

I need to copy every OleObject and/or Shape from one workbook to another.

right now I'm using the following code:

Please Login or Register  to view this content.

It works wonderfully for shapes/pictures, but when there is an OleObject (I tested it with commandButtons,OptionButtons, from the Excel Objects toolbars) it pops Up an Error(if you proceed line by line with F8. If just press F5, the error msg wont popUp).

The error pops Up on the ".....PasteSpecial" line.

[Leith Ross]

Hello Wizz,

This example macro copies only the OLE Objects from the source workbook to the destination workbook. As you can see, it is a bit more involved than copying Shapes.

However, this will not copy any code associated with the OLE object, only the object class and the shape associated with it. If both sheets have the same column widths and row heights, the copied OLE objects will be positioned properly on the worksheet. If not, the objects will appear out of place.

Please Login or Register  to view this content.

[Wizz]

Thanks Ross,
It worked great !

[snb]

Since ActiveX-controls can contain VBA-code I'd prefer to copy the sheet, containing all these controls (copying the controls won't suffice in that case). The only thing to do then is adapt the layout and/or content of the cells in that copied worksheet.

[Wizz]

Thanks snb and Leith for the hint about not copying any procedure.
In my case it is even a benefit. The source sheet must not have any macros/procedures. I just needed to import everything for visual representation. Any code imported could cause trouble in my main sheet.

[snb]

In that case:

Please Login or Register  to view this content.