• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I copy a Shape from one worksheet to another?

B

BobBridges

Active Member
I don't handle Shapes much, but I'm doing some automation for a user who's created a bunch of button to navigate from one worksheet to another, and he'd like me to copy a button to a new worksheet when I create it. I can find an example of the button on another worksheet easily enough, and I see various methods for copying it. The Copy method puts a copy of the shape on the clipboard; Pickup and Apply force an existing shape to take on the characteristics of another shape; and Duplicate returns a copy of a shape.

But when it comes to putting the new shape in the new worksheet, I don't see how. All the Add methods I see in the Shapes collection (and there are quite a few) require a handful of extra arguments that specify things about the new shape that I intend to copy from the old shape, including its position on the sheet. The AddShape method, for example, requires arguments for Type, Left, Right, Width and Height. Must I specify some fake values for them and then write over the shape with a Set statement or Apply method? Seems like wasted effort. Does anyone know the better way?

I also posted this question on the MSOffice forum, but past experience leads me to expect little help there; there are folks there who are very good indeed (better than me, anyway) with Excel functions and formulae, but when it comes to VBA there seems to be less help.
 
Hm, I called it a "Shape" because when I searched in the source worksheet for the "button" they push to get to the new worksheet, I found it in that worksheet's Shapes collection. So when I looked in the Excel object model, I was looking for how to add another Shape. I didn't even see that there is also a Button object. It may not be the same thing (and I should keep it the same for the sake of my client), but it's worth at least a look.

I'm going to be out all day, but tomorrow or Monday I'll check it out, thanks.
 
Nope, turns out that code snippet is more than eleven years old and the Buttons collection is no longer listed in the object model. I suppose they expanded the Button into Shapes. Anyone else?
 
Ah, the Buttons object doesn't exist but when I googled for the answer (as you reminded me to do), I found a solution. The key is to use a Copy method on the source shape, and then the Paste method on the target worksheet. I'll have to adjust the position—that didn't copy over—but the size, shape, color, label and hyperlink target all do, so that's good enough for my purposes. Thanks!
 
You must log in or register to reply here.
Back
Top