Excel 2007 – paste object via vba code

Topic

New Reply

I have the following piece of code attached to my Excel worksheet:

ActiveSheet.Shapes(“Object 407”).Select
Selection.Copy
Range(“BP106”).Select
ActiveSheet.Paste
The code is activated by making a choice in a drop down in the worksheet. The worksheet is password protected but the cell BP106 is not locked.

When I make a selection from the drop down, the shape is not being pasted into BP106. If I go to a new worksheet I can do a paste command and the shape is pasted.

If I put a breakpoint in the code, and manually step through the code, the shape is pasted into PB106 with no problem.

I can’t figure out why it works when I manually step through the code but not when the code runs automatically.

Any helpful thoughts?

Thanks!

PS: I also posted this question on June 28 on another site with no responses, so thought I would try here.

Reply | Quote

Replies

Not sure why, but it works OK if the sheet is NOT protected.
Try Turning Off Protection just before the Paste
Then Back on after

conPWD is a variable containing the Password.
For safety you could make it a Constant and store it in another Module

Const conPWD=”your password”

Code:

ActiveSheet.Shapes("Object 407").Select 
Selection.Copy
Range("BP106").Select
Activesheet.Unprotect conPWD
ActiveSheet.Paste 
Activesheet.Protect conPWD

When I used this technique it worked fine on a password protected sheet.

Reply | Quote

Adding the unprotect/protect didn’t help on my spreadsheet.

What I did find though —
The shape I’m copying is at the very bottom of the worksheet
When the paste isn’t showing up in cell BP106, I found that the paste is being done at the bottom of the worksheet in the same location it is copying the shape from.
So the selection of the cell BP106 isn’t working, unless I step through the code manually.

I still have not figured out why, but at least I now know it is the selection of the cell that is not working.

Anyone have any thoughts on what I am missing here?

Thanks,
Patty

Reply | Quote

I do have a split screen spreadsheet. The bottom half has input fields that feed into the top half, and the screen is split so the users can see both sections of the worksheet at the same time.

I put a breakpoint at the very last line of code (which moves the cursor to another cell input cell in the worksheet, after the shape has been pasted), and notice that if I scroll the bottom half of the split screen to show PB106 the cell is selected but the shape is not pasted into it. The shape is pasted at the location of the original shape. BP106 in the top half of the split screen does not show selected at this same point.

Reply | Quote

Never mind the split screen confusion. I took out the split screen with the same result. It pastes the shape in cell BP106 if I go through the code manually. If I don’t set a breakpoint and let it run, the shape is pasted at the original location instead of in BP106.

I tried putting in msgboxes before and after the select and paste commands, to see if slowing it down made any difference. It didn’t. It still didn’t paste into BP106.

Reply | Quote

I found a way to make it work!

I moved my code around to look like the following:
Worksheets(“front page”).Range(“BP106”).Select ‘selects the range to be copied into before copying the shape
ActiveSheet.Shapes(“Object 407”).Copy ‘copy the shape without selecting it
ActiveSheet.Paste ‘then the shape pastes into BP106 whether running automatically or manually stepping through this code

Hope I haven’t wasted anyone’s time. Persistence has paid off.

Thanks to all who have looked at and contemplated this problem for me.

Reply | Quote