Avatar of Senechal
Senechal

asked on 

copy paste embedded shapes between hidden worksheets

Hello,

The code below has been implemented to:

1. delete existing OLE shapes from hidden worksheet 1
2. copy new OLE shapes from hidden worksheet 2
3. paste the copied shapes in hidden worksheet 1

The command button is on a worksheet 3, which is the only visible sheet to the user.

The code works fine for steps 1 and 2, but doesn't work for step 3 (pasting the shapes to hidden worksheet 1).

The code is as follows:

Private Sub CommandButton1_Click()


 ThisWorkbook.Worksheets("6").Shapes.Range(Array("1", "2", "3", "4", "5")).Delete

 
    ThisWorkbook.Worksheets("7").Shapes.Range(Array("1", "2", "3", "4", "5")).Select
    Selection.Copy
   
       ThisWorkbook.Worksheets("6").Select
    Selection.Paste

End Sub


The answer appears so close - all help appreciated.
Microsoft Excel

Avatar of undefined
Last Comment
Jacques Geday
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

replace this
Selection.Paste

by this
Selection.PasteSpecial

gowflow
Avatar of Senechal
Senechal

ASKER

Hello gowflow,

Thank you for the suggestion, but the results are still the same as before: the deletion takes place OK, but not the copy and paste. I'm attaching a demo file in case this is any help.
DemoCopyPasteOLE.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Senechal
Senechal

ASKER

Hi goflow,

Your solution works perfectly. Thank you so much.
Avatar of Senechal
Senechal

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for Senechal's comment #a39654737

for the following reason:

Solution by goflow.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

I am objecting to this closure as supposed the asker made a mistake in closing it by awarding himself 0 points but no points to the correct answer. His comments clearly indicate that I provided the solution for him.
Tks/gowflow
Avatar of Senechal
Senechal

ASKER

Gowflow provided an excellent solution and I cannot thank him enough for his courtesy and speed. It is absolutely worth 500 points. This is the first time I've used this system, so the mistake in point-setting was entirely mine.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

No problem don't worry I thought so. Pls feel free to put any comment on this question case you need further help with any other question. I will keep this one monitored.
gowflow
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo